Chapter 7 - Managing Security

Operations in any business is a coordinated system of people, equipment, furniture, facilities, and activities. While providing functional necessities that allow employees to do their jobs, the system has controls to ensure that only individuals with the proper authority and expertise perform some activities. For example, salary increases and bonuses are usually determined or approved only by the person responsible for the affected budget.

The same is true for company information. Some information can be shared with anyone in the company, or even with the general public. More sensitive and confidential information is kept in locked file cabinets or underground vaults. A person must have the proper credentials to access this information.

Although the term security is commonly used in the context of theft prevention, it can also save people from making costly mistakes; placing an important file folder in a locked cabinet, for example, prevents someone from mistakenly recycling it with the rest of the scrap paper in the office. Within the operations of a company, a database provides a mechanism to store, manage, and control information; the database becomes the locked cabinet.

It may be reasonable for a database developer to trust that coworkers will use the database only as it is intended to be used; that they will use only the intended applications to access data; and that those applications are properly designed and implemented to work correctly in the database. In this case, the simplest security mechanism is to give everyone full access to everything in the database.

However, a well-intentioned coworker experimenting with Microsoft SQL Server may log in and accidentally delete a year's worth of sales transactions. Users may also attempt to gain unauthorized access to sensitive personnel or customer data. Finally, miscommunication between the database developer and application developer may result in the application deleting all active customer information when the user had intended to delete inactive customer data. Unintentional mistakes and unauthorized data access can come at a high cost to a company.

It is critical that a database developer make no assumptions regarding what users will be doing in the database. In addition to controls built into applications, the database itself must have a solid security system to control what activities can be performed and what information can be seen and changed. This approach to security ensures the protection of data, regardless of how users get into the database or what they do while there.

This section describes the security tools built into SQL Server version 7.0, and includes information about:

  • Security architecture. 

  • Setting up security accounts. 

  • Managing security accounts. 

  • Managing permissions. 

  • Planning security. 

  • Advanced security topics. 

Security Architecture

The architecture of a security system is based on users and groups of users, referred to as Security Principals. This illustration shows how users and local and global groups in Microsoft Windows NT can map to security accounts in Microsoft SQL Server and how SQL Server can handle security accounts independently of the accounts in Windows NT.

Cc917569.ntsctree(en-us,TechNet.10).gif 

The CORPUSERS local group contains two users and a global group, Mktg, which also contains two users. SQL Server allows Windows NT local and global groups to be used directly to organize its user accounts. Additionally, the Windows NT users Fred and Jerry, not part of a Windows NT group, can be added to SQL Server either directly as a Windows NT user (Fred for example), or as a SQL Server user (Jerry).

SQL Server extends the above model further with the use of roles. Roles are groups of users organized for administrative purposes, similarly to Windows NT groups. Roles can be used to organize users where an equivalent Windows NT group does not exist. For example, the Managers role contains the Windows NT Mktg global group, and the Windows NT users Frank and Fred.

SQL Server also provides security at the application level through the use of individual database application roles.

For more information about Windows NT users and groups, see your Windows NT documentation.

See Also 

In This Volume 

Setting Up Security Accounts

How SQL Server Implements Security

A user passes through two stages of security when working in Microsoft SQL Server: authentication and permissions validation. The authentication stage identifies the user using a login account and verifies only the ability to connect with SQL Server. If authentication is successful, the user connects to SQL Server. The user then needs permissions to access databases on the server, which is done by using an account in each database, mapped to the user login. The permissions validation stage controls the activities the user is allowed to perform in the SQL Server database.

Authentication

Microsoft SQL Server can operate in one of two security (authentication) modes:

  • Windows NT Authentication Mode (Windows NT Authentication) 

  • Mixed Mode (Windows NT Authentication and SQL Server Authentication) 

Mixed Mode allows users to connect using Windows NT Authentication or SQL Server Authentication. Users who connect through a Microsoft Windows NT user account can make use of trusted connections (connections validated by Windows NT) in either Windows NT Authentication Mode or Mixed Mode. After successful connection to SQL Server, the security mechanism is the same for both modes.

Security systems based on SQL Server logins and passwords (SQL Server Authentication) may be easier to manage than security systems based on Windows NT user and group accounts, especially for databases that are not mission critical and applications without sensitive and confidential information. For example, a single SQL Server login and password can be created for all users of an application, rather than creating all the necessary Windows NT user and group accounts. This does, however, remove the ability to track and control the activities of individual users and is therefore not recommended.

Windows NT Authentication

When a user connects through a Windows NT user account, SQL Server verifies that the account name and password were validated when the user logged on to Windows NT or Microsoft Windows 95/98.

SQL Server achieves login security integration with Windows NT by using the security attributes of a network user to control login access. A user's network security attributes are established at network login time and are validated through a sophisticated password encryption mechanism. When a network user tries to connect, SQL Server uses Windows NT-based facilities to determine the validated network username. SQL Server then permits or denies login access based on that network username alone, without requiring a separate login name and password.

Login security integration operates over network protocols that support authenticated connections between clients and servers. Such connections are referred to as trusted connections and are supported by SQL Server using any available network library.

Note If a user attempts to connect to SQL Server providing a blank login name, SQL Server automatically uses Windows NT Authentication. Additionally, if a user attempts to connect to a SQL Server configured for Windows NT Authentication Mode, using a specific login, the login is ignored and Windows NT Authentication is used.

Cc917569.intsec(en-us,TechNet.10).gif

Windows NT Authentication has certain benefits over SQL Server Authentication, primarily due to its integration with the Windows NT security system. Windows NT security provides more features, such as secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after multiple invalid login requests.

Because Windows NT users and groups are maintained only by Windows NT, SQL Server reads information about a user's membership in groups when the user connects. If changes are made to the accessibility rights of a connected user, the changes become effective the next time the user connects to SQL Server or logs on to Windows NT (depending on the type of change).

Note Windows NT Authentication Mode is not available when SQL Server is running on Microsoft Windows 95/98.

Security Account Delegation 

Security account delegation allows clients connected to one server using Windows NT Authentication to access another server by using a remote procedure call (RPC) with his or her security credentials, rather than the security credentials of the first server, or predefined security credentials set up on the first server. This allows more appropriate auditing because the user's login, rather than a mapped login, is used for all connections, including RPCs. It also allows for a simpler security environment because server to server login mapping for users is not required. SQL Server can make use of security delegation, if available, when making RPCs, such as executing a distributed query using a SQL Server-linked server.

For more information about security account delegation, see your Windows NT documentation.

SQL Server Authentication 

When a user connects with a specified login name and password from a nontrusted connection, SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches the one previously recorded. If SQL Server does not have a login account set, authentication fails and the user receives an error.

SQL Server Authentication is provided for backward compatibility because applications written for earlier versions of SQL Server may require the use of SQL Server logins and passwords. Additionally, SQL Server Authentication is required when SQL Server is running on Windows 95/98 because Windows NT Authentication Mode is not supported on Windows 95/98. Therefore, SQL Server uses Mixed Mode when running on Windows 95/98 (but supports only SQL Server Authentication).

Application developers and database users may prefer SQL Server Authentication because they are familiar with the login and password functionality. SQL Server Authentication is also required for connections with internet and clients other than Windows NT clients.

Cc917569.permdiag(en-us,TechNet.10).gif 

Note When connecting to a SQL Server running on Windows NT using Named Pipes, the user must have permission to connect to the Windows NT Named Pipes IPC, \\<computername>\IPC$. If the user does not have permission to connect, it is not possible to connect to SQL Server using Named Pipes unless either the Windows NT guest account on the computer is enabled (disabled by default), or the permission "access this computer from the network" is granted to everyone.

See Also 

In Other Volumes 

"Backward Compatibility" in Microsoft SQL Server Introduction 

Permissions Validation

After a user has been authenticated and allowed to log in to Microsoft SQL Server using their login, a user account is required in each database the user needs to access. Requiring a user account in each database prevents users from being able to connect to SQL Server and access all the databases on a server.

Cc917569.login(en-us,TechNet.10).gif

For example, if a server contains a personnel database and a recruiting database, users who should be able to access the recruiting database but not the personnel database would have a user account created only in the recruiting database.

The user account in each database is used to apply security permissions for the objects (tables, views, stored procedures and so on) in that database. This user account can be mapped from Microsoft Windows NT user accounts, Windows NT groups in which the user is a member, or SQL Server login accounts. If there is no account mapped directly, the user may be allowed to work in a database under the guest account, if one exists. The activities a user is allowed to perform are controlled by the permissions applied to the user account used to gain access to a database.

SQL Server accepts commands after a user has successfully gained access to a database. The user may enter ad hoc commands or choose menu options in an application. All activities a user performs in a database are communicated to SQL Server through Transact-SQL statements. When SQL Server receives a Transact-SQL statement, it ensures the user has permission to execute the statement in the database. If the user does not have the proper permissions, either to execute a statement or access an object used by the statement in the database, SQL Server returns a permissions error.

Hierarchical Security Management

The security environment in Microsoft Windows NT and Microsoft SQL Server is stored, managed, and enforced through a hierarchical system of users. To simplify the administration of many users, Windows NT and SQL Server use groups and roles. A group is an administrative unit within Windows NT that contains Windows NT users or other groups. A role is an administrative unit within SQL Server that contains SQL Server logins, Windows NT logins, groups, or other roles. Arranging users into groups* and roles makes it easier to grant or deny permissions to many users at once. The security settings defined for a group are applied to all members of that group. When a group is a member of a higher-level group, all members of the group inherit the security settings of the higher-level group, in addition to the security settings defined for the group *itself or user accounts.

The organizational chart of the security system often corresponds to the organizational chart of a company.

Cc917569.orgcht1(en-us,TechNet.10).gif

Cc917569.orgcht2(en-us,TechNet.10).gif

The organizational chart for a company is a good way of representing the security model for the company, but there is one rule for a company's organizational hierarchy that does not apply to the security model: Common business practices usually dictate that an individual can report only to one manager. This rule implies that an employee can fall into only a single branch of the hierarchical model, as shown in the diagram.

The requirements of a database security system go beyond this one-manager limitation; employees commonly need to belong to security groups that do not fall within the strict organizational plan of the company. Employees such as administrative staff exist in every branch of the company and require security permissions regardless of organizational branch. To support this broader model, the security system in Windows NT and SQL Server allows groups to be defined across a hierarchy. An Administrative group can be created to contain administrative employees for every branch of the company from the Corporate group to the Payroll group.

This hierarchical system of security groups simplifies management of security settings. It allows security settings to be applied collectively to all group members, without having to be defined redundantly for each person. The hierarchical model also accommodates security settings applied only to a single user.

Setting Up Security Accounts

Each user needs to gain access to Microsoft SQL Server through a login account that establishes the ability to connect (authentication). This login then has to be mapped to a SQL Server user account used to control activities performed in the database (permissions validation). Therefore, a single login is mapped to one user account created in each database the login has to access. If no user account exists in a database, the user cannot access the database even though the user may be able to connect to SQL Server.

The login is created in Microsoft Windows NT rather than in SQL Server. This login (a Windows NT user or group account) is then granted permission to connect to SQL Server.

The login is created within SQL Server.

The SQL Server user accounts that map to logins (created in Windows NT or SQL Server), and permit access to the database, are always created within each SQL Server database.

Rules for SQL Server Logins, Users, Roles, and Passwords

Microsoft SQL Server logins, users, roles, and passwords can contain from 1 to 128 characters, including letters, symbols, and digits, (for example Andrew-Fuller, Margaret Peacock, or 139abc). Therefore, Microsoft Windows NT or Microsoft Windows 95/98 usernames can be used as SQL Server logins. However, certain symbols can be used only in Transact-SQL statements if the login, user, role, or password is delimited with double quotation marks ("), or square brackets ([ ]). Use delimiters in Transact-SQL statements when the SQL Server login, user, role, or password:

  • Contains, or begins with, a space character. 

  • Begins with the $ or @ character. 

Note It is not necessary to specify delimiters when entering logins, users, roles, and passwords into the text boxes of the SQL Server graphical client tools, such as SQL Server Enterprise Manager.

Additionally, a SQL Server login, user, or role cannot:

  • Contain a backslash (\) character, unless referring to an existing Windows NT user or group. The backslash separates the Windows NT computer or domain name from the username. 

  • Already exist in the current database (or master, for logins only). 

  • Be NULL, or an empty string (""). 

See Also 

In Other Volumes 

"Delimited Identifiers" in Microsoft SQL Server Database Developer's Companion 

Adding a Windows NT User or Group

Existing Microsoft Windows NT accounts (users or groups) are granted permissions to connect to Microsoft SQL Server before they can access a database. If all members of a Windows NT group will be connecting to SQL Server, you can grant permission to connect to SQL Server for the group as a whole. Managing group permissions is much easier than redundantly managing permissions for individual users. If a Windows NT group should not be granted permission collectively, you can grant permission to connect to SQL Server for each individual Windows NT user.

Users 

When granting a Windows NT user access to connect to SQL Server, specify the Windows NT domain or computer name the user belongs to, followed by a backslash, and then the user. For example, to grant access to the Windows NT user Andrew, in the Windows NT domain LONDON, specify LONDON\Andrew as the username.

Local and Global Groups 

There are two Windows NT groups: local and global.

Global groups contain user accounts from the Windows NT Server domain in which they are created. Global groups cannot contain other groups or users from other domains and cannot be created on a computer running Windows NT Workstation.

Local groups can contain user accounts and global groups from the domain in which they are created and in any trusted domain. Local groups cannot contain other local groups.

Additionally, Windows NT has predefined, built-in local groups, for example Administrators, Users, and Guests. By default, these built-in groups are always available on any Windows NT computer, unless they are explicitly removed.

When granting a Windows NT local or global group access to connect to SQL Server, specify the domain or computer name the group is defined on, followed by a backslash, and then the group name. However, to grant access to a Windows NT built-in, local group, specify BUILTIN instead of the domain or computer name. For example, to grant access to a global group called SQL_Users, in the LONDON domain, specify LONDON\SQL_Users as the group name to add to SQL Server. However, to grant access to the built-in Windows NT local group Administrators, specify BUILTIN\Administrators as the group name to add to SQL Server.

For more information about Windows NT accounts, see your Windows NT documentation.

Granting a Windows NT User or Group Access to a Database

To obtain access to a Microsoft SQL Server database, a Microsoft Windows NT user or group must have a corresponding user account in each database they need to access, and to which permissions are applied.

It is not necessary to add an individual user account in a database for each Windows NT user in a Windows NT group whose members all perform the same activities. Add an account for the group rather than for each member. When Windows NT group members need to work in a database, they are granted access through their membership in the Windows NT group; there is not a specific account for individual Windows NT users within the group. For example, a Windows NT group London\Managers contains the Windows NT user London\JoeB. The SQL Server system administrator grants login access only to London\Managers. The owner of database Accounts grants only London\Managers permission to access Accounts. Although London\JoeB does not have explicit permission granted to connect to SQL Server or access Accounts, he can connect to SQL Server and access Accounts due to his membership in London\Managers.

An individual Windows NT user needs to be added to a database only if the user performs activities different from other members of any Windows NT group; for example, special database administrative duties.

Note Users who are granted access to SQL Server through their memberships in a Windows NT group do not have entries for their individual Windows NT user accounts in the system tables. However, an entry is created for their individual Windows NT user accounts if they create objects, such as a table or a stored procedure, in a SQL Server database.

It is possible to grant a Windows NT user or group access to a database without explicitly granting the user or group access to connect to SQL Server first. Provided that the Windows NT user or group has permission to connect to SQL Server when they need to access the database, database access can be granted before login access is granted. For example, if the Windows NT group London\Managers is granted access to the Accounts database, but the user London\JoeB, a member of London\Managers, does not have login access to SQL Server, London\JoeB cannot access Accounts. However, as soon as London\JoeB is granted login access to SQL Server, he can also access Accounts.

Adding a SQL Server Login

If Microsoft SQL Server is configured to operate in Mixed Mode, or when SQL Server is running on Microsoft Windows 95/98, adding SQL Server login accounts that allow connection with a specified login name and password, rather than through a Microsoft Windows NT user or group account, can be a preferred method of authenticating users. This can be required:

  • For compatibility with applications created for earlier SQL Server versions not designed to work with Windows NT accounts. 

  • For applications designed to work with general users who do not have Windows NT accounts. 

  • To connect to SQL Server running on Windows 95/98 because Windows NT Authentication is not available on Windows 95/98. 

System Administrator (sa) Login

System administrator (sa) is a special login. By default, it is assigned to the sysadmin fixed server role and cannot be changed. It is provided for backward compatibility with earlier versions of Microsoft SQL Server. Although sa is a built-in administrator login, it should not be used routinely. Instead, system administrators should be made members of the sysadmin fixed server role, and log on using their own logins instead. Use sa only when there is no other way to log in to SQL Server; for example, when other system administrators are unavailable, or have forgotten their passwords.

Note When SQL Server is installed, the sa login is not assigned a password. It is recommended that the password be assigned immediately to prevent unauthorized access to SQL Server using the sa login.

See Also 

In This Volume 

Assigning an sa Password

Granting a SQL Server Login Access to a Database

A Microsoft SQL Server user account should be added to each database for each SQL Server login that requires access to the database (the same as adding a user account for a Microsoft Windows NT user or group). If a user is not created in the database, the SQL Server login cannot access the database.

Important To grant a SQL Server login access to a database, the SQL Server login must already exist. This is different from Windows NT users and groups that can be granted access to a database before they are granted access to connect to SQL Server.

Windows NT groups allow multiple Windows NT users to be granted access to a database in one step, simplifying administration, whereas SQL Server logins must be granted access to a database one at a time.

Database Owner (dbo)

Any member of the sysadmin fixed server role is mapped to a special user inside each database called dbo. Any object created by any member of the sysadmin fixed server role belongs to dbo automatically.

For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1.

Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1.

The dbo cannot be deleted.

Important Only objects created by members of the sysadmin fixed server role belong to dbo. Objects created by any other user (including members of the db_owner fixed database role) who is not also a member of the syadmin fixed server role:

  • Belong to the user creating the object, not dbo

  • Be qualified with the name of the user who created the object when it is referenced by users who did not create the object. 

See Also 

In Other Volumes 

"Delimited Identifiers" in Microsoft SQL Server Database Developer's Companion 

"sp_changedbowner" in  Microsoft SQL Server Transact-SQL and Utilities Reference 

Database Object Owner

Database objects are tables, indexes, views, triggers, and stored procedures. The user who creates a database object is the database* object *owner. The database owner or system administrator first must grant the user permission to create a particular type of object. The database object owner can then create an object and grant other users permission to use that object.

Database object owners have no special login IDs or passwords. The creator of a database object is implicitly granted all permissions but explicitly must grant permissions to other users before they can access the object.

When users access an object created by another user, the object should be qualified with the name of the object owner; otherwise, Microsoft SQL Server may not know which object to use because there could be many objects of the same name owned by different users. If an object is not qualified with the object owner when it is referenced, for example my_table instead of owner.my_table, SQL Server looks for an object in the database in the following order:

  1. Owned by the current user. 

  2. Owned by dbo

If the object is not found, an error is returned.

For example, user John is a member of the db_owner fixed database role, but not the sysadmin fixed server role, and creates table T1. All users, except John, who want to access T1 must qualify T1 with the username John. If T1 is not qualified with the username John, SQL Server first looks for a table named T1 owned by the current user, and then owned by dbo. If the current user and dbo do not own a table named T1, an error is returned. If the current user or dbo owns another table named T1, the other table named T1, rather than John.T1 is used.

If a database object owner must be removed from a database, the owned objects must first be dropped or their ownership must be transferred to another user.

Note SQL Server allows all members of a role or Windows NT group to be specified as the owner of an object. For example, to create the table group_table owned by the Windows NT group LONDON\Users, specify [LONDON\Users].group_table as the qualified table name. All members of the LONDON\Users group have database object owner permissions on group_table.

See Also 

In Other Volumes 

"Delimited Identifiers" in Microsoft SQL Server Database Developer's Companion 

"sp_changeobjectowner" in  Microsoft SQL Server Transact-SQL and Utilities Reference 

guest User

The guest user account allows a login without a user account to access a database. A login assumes the identity of the guest user when all of the following conditions are met:

  • The login has access to Microsoft SQL Server, but does not have access to the database through his or her own user account. 

  • The database contains a guest user account. 

Permissions can be applied to the guest user as if it were any other user account. The guest user can be deleted and added to all databases except master and tempdb, where it must always exist. By default, a guest user account does not exist in newly created databases.

For example, to add a guest user account to a database named Accounts, execute from SQL Server Query Analyzer:

USE Accounts
GO
EXECUTE sp_grantdbaccess guest, guest

Creating User-Defined SQL Server Database Roles

When a group of users needs to perform a specified set of activities in Microsoft SQL Server and there is no applicable Microsoft Windows NT group, or if you do not have permissions to manage Windows NT user accounts, you can add a SQL Server role in the database.

For example, a company may form a Charity Event Committee involving employees from different departments and from several different levels in the organization. These employees need access to a special project table in the database. There is no existing Windows NT group that includes just these employees, and there is no other reason to create one in Windows NT. A custom SQL Server database role, CharityEvent, could be created for this project and individual Windows NT users added to the database role. When permissions are applied, the users in the database role gain table access. Permissions for other database activities are not affected, and they are the only ones who can work with the project table.

SQL Server roles exist within a database and cannot span more than one database.

The advantages of using database roles include:

  • For any user, more than one database role can be active at any time. 

  • SQL Server roles can contain Windows NT groups and users, and SQL Server users and other roles, providing all users, groups, and roles exist in the current database. 

  • A user can belong to more than one role in the same database. 

  • A scalable model is provided for setting up the correct level of security within a database. 

Because users can belong to more than one database role at a time, it is not necessary for users to assume the identity (and permissions) of other users through temporary aliases.

Note A database role is owned by either the user explicitly specified as the owner when the role is created, or the user who created the role when no owner is specified. The owner of the role determines who can be added or removed from the role. However, because a role is not a database object, multiple roles of the same name in the same database owned and by different users cannot be created.

See Also 

In Other Volumes 

"Backward Compatibility Details (Level 4)" in Microsoft SQL Server Introduction 

Adding a Member to a SQL Server Database Role

When you add a new user account in Microsoft SQL Server, or you need to change the permissions of an existing user, you can add the user to a SQL Server database role rather than applying permissions directly to the account. SQL Server users, Microsoft Windows NT users, Windows NT groups, and other SQL Server database roles all can be added as a member of a role. Because a role is restricted to a single database, you can add users, groups, and roles known only to that database. It is not possible to add users, groups, or roles from one database to a role in another database. Roles can simplify security administration in databases with a large number of users or with a complex security system.

Note When you add a Windows NT login without a user account in the database to a SQL Server database role, SQL Server creates a user account in the database automatically.

A user account can be a member of any number of roles within the same database. For example, a SQL Server user can be a member of the admin role and the users role for the same database, with each role granting different permissions. The effective permissions on an object granted to a member of more than one role are the cumulative permissions of the roles, although a denied permission in one role has precedence over the same permission granted in another role. For example, the admin role may grant access to a table, whereas the users role denies access to the same table. A member of both roles is denied access to the table because denied access is the most restrictive.

Users to be added to a user-defined database role must already have permission to access the database containing the user-defined role.

Adding a Member to a Predefined Role

The security mechanism in Microsoft SQL Server includes several predefined roles with implied permissions that cannot be granted to other user accounts. If you have users who require these permissions to perform the special activities supported by these roles, you must add their accounts to these predefined roles. The two types of predefined roles are fixed* server and fixed *database.

Fixed server roles, such as sysadmin, are defined at the server level and exist outside of individual databases. To add a user as a member of a fixed server role, the user must have a SQL Server or Microsoft Windows NT login account. Any member of a fixed server role can add other logins.

Important Windows NT users who are members of the BUILTIN\Administrators group are members of the sysadmin fixed server role automatically.

Fixed server role

Description

sysadmin

Performs any activity in SQL Server.

serveradmin

Configures server-wide settings.

setupadmin

Adds/removes linked servers, and execute some system stored procedures, such as sp_serveroption.

securityadmin

Manages server logins.

processadmin

Manages processes running in SQL Server.

dbcreator

Creates and alters databases.

diskadmin

Manages disk files.

The permissions of the sysadmin fixed server role span all of the other fixed server roles.

Note It is not possible to create new fixed server roles. Roles can be created only at the database level.

Fixed database roles, such as db_owner, are defined at the database level and exist in each database. You can add any valid user account (Windows NT user or group, or SQL Server user or role) as a member of a fixed database role. Each member gains the permissions applied to the fixed database role. Any member of a fixed database role can add other users to the role.

Fixed database role

Description

db_owner

Performs the activities of all database roles, as well as other maintenance and configuration activities in the database.

db_accessadmin

Adds or removes Windows NT groups, Windows NT users, and SQL Server users in the database.

db_datareader

Sees all data from all user tables in the database.

db_datawriter

Adds, changes, or deletes data from all user tables in the database.

db_ddladmin

Adds, modifies, or drops objects in the database.

db_securityadmin

Manages roles and members of SQL Server database roles, and can manage statement and object permissions in the database.

db_backupoperator

Backs up the database.

db_denydatareader

Sees no data in the database.

db_denydatawriter

Changes no data in the database.

The permissions of the db_owner fixed database role span all of the other fixed database roles.

public Role

The public role is a special database role to which every database user belongs. The public role:

  • Captures all default permissions for users in a database. 

  • Cannot have users, groups, or roles assigned to it because they belong to the role by default. 

  • Is contained in every database, including master, msdb, tempdb, model, and all user databases. 

  • Cannot be dropped. 

Using the Create Login Wizard

Although the steps required to grant login access to Microsoft SQL Server and a database can be performed separately, the Create Login Wizard can simplify the process. The Create Login Wizard:

  • Allows the user to choose which authentication mode to use to connect to SQL Server (Windows NT Authentication Mode or Mixed Mode). 

  • Adds a Microsoft Windows NT or SQL Server login. 

  • Adds a Windows NT or SQL Server user to a fixed server role. 

  • Adds a Windows NT or SQL Server user to one or more databases, thereby granting the user access to those databases. 

Managing Security Accounts

After security accounts have been added to Microsoft SQL Server, you can modify them as business needs change. This usually involves viewing, modifying, and removing the security accounts in the database to fit the needs of your business.

Viewing Logins

Viewing Microsoft SQL Server logins can help you determine if a user or Microsoft Windows NT group has permission to connect to SQL Server and which databases the login can access. Viewing a login before removing it can be used to show which database users must be removed first (because it is not possible to remove a login without first removing the associated users).

Information that can be viewed about each login includes:

  • Users in each database associated with the login. 

  • Default database and language the login uses when the user first connects to SQL Server. 

  • Windows NT security identifier (SID). 

Note It is not possible to view the password of any login unless the password is NULL. Passwords are encrypted when stored in SQL Server.

Viewing Database Users

Viewing a Microsoft SQL Server user account in a database shows the roles of which the user is a member, the SQL Server login associated with the user, and the default database. You can use this information to gain an understanding of how the user fits into the security system of the database.

Modifying Logins

After a login has been created, it may be necessary to change the password, default database, or default language. For example, a user may forget his or her password or want to change the password for security reasons, need to use a different database on a regular basis, or need to see messages in a different language.

Note If a user forgets a password, a member of the sysadmin fixed server role can change the password without knowing the original password. A user cannot change a password if he or she has forgotten it.

Removing Logins and Users

When employees leave a company, the process of deactivating their security accounts is similar to the process of adding a new user. The security mechanism in Microsoft Windows NT should be updated first by removing the user's Windows NT user account (if they have one). If the user has a Microsoft SQL Server user account, it should be removed from SQL Server along with any SQL Server database roles specifically defined for that user. Finally, any SQL Server login should be removed.

Removing a SQL Server user or Windows NT user or group from a SQL Server database automatically removes the permissions defined for the user or group and prevents that user from using the database under the old security account. The permissions do not have to be removed separately. However, it is not possible to remove a user from SQL Server if that user currently owns objects (tables, procedures, or views) within a database. If the user does own objects, then either drop those objects before removing the user, or transfer ownership to another existing user by using the sp_changeobjectowner system stored procedure.

Removing a user does not remove a login automatically, so it does not prevent connection to SQL Server. After being removed, the user can log in to the databases only through the guest account and perform activities under those permissions. To prevent a user from connecting to SQL Server, remove his or her login.

If a linked server login is set up and is no longer required, remove it to prevent unauthorized access to the linked server and to keep the security system as simple as possible.

See Also 

In Other Volumes 

"sp_changeobjectowner" in  Microsoft SQL Server Transact-SQL and Utilities Reference 

Denying Login Access to Windows NT Accounts

When a Microsoft Windows NT user belongs to a Windows NT group that has a login account in Microsoft SQL Server, the user is allowed to connect through the group login. However, there may be times when you must ensure that a Windows NT user, or members of a Windows NT group, are prevented from connecting to SQL Server. You can deny login access to any Windows NT user or group. Users cannot connect to SQL Server if their user account, or any group in which they are a member, has been denied login access.

Viewing Roles

There are several points in the process of creating and using a database when you may need to find information about a Microsoft SQL Server database role or a fixed server role. For example, you may need to see what roles exist in the current database, or list the fixed server roles.

Viewing and Modifying Role Memberships

While using a database you may need to list the members of a database role or fixed server role. Additionally, when a Microsoft SQL Server user no longer needs the permissions from a user-defined, fixed database or server role of which he or she is a member, you can remove the user from the role to keep the security system as simple as possible.

Removing a SQL Server Database Role

The changing security requirements of a database can render a Microsoft SQL Server database role obsolete. You should remove roles when you have removed all users and are relatively certain that the role and its permissions will not be required in the future. Empty roles can be saved if the permissions may be required for a new user. From an administrative perspective, it is much easier to work with a security system that is not cluttered with unnecessary security roles. SQL Server operates faster with a simpler security system, although it is probably not a problem unless there are an extremely large number of roles.

Note Avoid deep levels of nested roles because this can affect performance.

Note It is not possible to remove fixed server roles or fixed database roles.

Managing Permissions

When users connect to Microsoft SQL Server, the activities they can perform are determined by the permissions granted to their security accounts, Microsoft Windows NT groups, or role hierarchies to which their security accounts belong. The user must have the appropriate permissions to perform any activity that involves changing the database definition or accessing data. Some activities, such as those that do not change items in the database or access data, do not require permissions.

Object Permissions 

Working with data or executing a procedure requires a class of permissions known as object permissions*.* Object permissions are based on a table, view, or stored procedure and control the ability to execute the SELECT, INSERT, UPDATE, and DELETE statements against the table or view, or the EXECUTE permission on a stored procedure. For example, if a user needs to be able to retrieve data from an entire table, then the SELECT object permission should be granted to that user.

Object permissions are:

  • SELECT, INSERT, UPDATE, and DELETE statement permissions, which can be applied to the entire table and view. 

  • SELECT and UPDATE statement permissions, which can be selectively applied to individual columns of a table or view. 

  • INSERT and DELETE statement permissions, which affect the entire row, and therefore can be applied only to the table and view and not to individual columns. 

  • EXECUTE statement permissions, which affect only stored procedures. 

Statement Permissions 

Activities involved with creating a database or an item in a database, such as a table or stored procedure, require a different class of permissions called statement* permissions.* For example, if a user must be able to create a table within a database, then the CREATE TABLE statement permission should be granted to the user. Statement permissions, such as CREATE DATABASE, are applied to the statement itself, rather than to a specific object defined in the database.

Statement permissions are:

CREATE DATABASE

CREATE DEFAULT

CREATE PROCEDURE

CREATE RULE

CREATE TABLE

CREATE VIEW

BACKUP DATABASE

BACKUP LOG

Implied Permissions 

The last class of activities controlled by permissions in SQL Server are those activities that can be performed only by members of predefined system roles or owners of database objects. For example, a member of the sysadmin fixed server role automatically inherits full permission to do or see anything in a SQL Server installation. The sysadmin role has permissions that cannot be changed and implied* *permissions, such as the ability to configure the SQL Server installation, that cannot be applied to other user accounts.

Database object owners also have implied permissions allowing them to perform all activities with the object they own. For example, a user who owns a table can view, add, or delete data, alter the table definition, or control permissions that allow other users to work with the table.

Allowing Access by Granting Permissions

You can grant statement and object permissions that allow a user account to perform activities or work with data in the current database.

It is important to be careful with the permissions granted to a user or group, especially when working with large, complex security systems and sensitive data. You must be certain that the permissions allow the users to perform their necessary activities in the current database, but restrict them from activities or information not part of their intended function.

For example, you may be inclined to grant SELECT object permission on the payroll table to all members of the personnel role, allowing all members of personnel to view payroll. Months later, you may overhear members of personnel discussing management salaries, information not meant to be seen by all personnel * *members. In this situation, you would grant SELECT access to personnel for all columns in payroll except the salary column.

The same theory applies to statement permissions. You may have a project lead who needs a new database. You can grant CREATE DATABASE permission to the project lead, and then find later that all the space on the server has been used because the project lead has created many databases while experimenting with various database options. A better solution in this case would be to create a database for the project lead and then grant CREATE TABLE and CREATE PROCEDURE permissions to the project lead in the new database, or make the project lead a member of the db_owner fixed database role in the database.

Note It is possible to grant permissions only to user accounts in the current database, for objects in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database. System stored procedures are the exception because EXECUTE permissions are already granted to the public role, which allows everyone to execute them. However, after EXECUTE has been issued, the system stored procedures check the user's role membership. If the user is not a member of the appropriate fixed server or database role necessary to run the stored procedure, the stored procedure will not continue.

Preventing Access by Denying Permissions

Microsoft SQL Server allows Microsoft Windows NT users, Windows NT groups, SQL Server users, and SQL Server database roles to be members of other roles. This results in a hierarchical security system allowing permissions to be applied through several levels of roles and members. But there may be times when you want to limit a certain user's or role's permissions. When you deny permissions on a user account, you:

  • Remove permission granted previously to the user, group, or role. 

  • Deactivate permission inherited from another role(s). 

  • Ensure that a user, group, or role will not inherit permission from a higher level group or role in the future. 

For example, you may need to provide all tenured employees in your company with access to several tables in a database, with the exception of a few new employees scattered throughout the organization who must be prevented from seeing the CorporateSecrets table.

You can create a role for each department in the company and add all employees to their department role. You can then create a company-wide Corporate role, to which you add each of the individual department roles and grant permissions to view the tables. At this point, every employee in the company can see all the tables because they inherit permission from the Corporate role through their department roles.

To selectively prevent employees from seeing CorporateSecrets, create a Nonsecure role, and add the individual employees who should not see the table. When you deny permission to view CorporateSecrets to Nonsecure, this access is removed from all members of Nonsecure, and the rest of the employees in the company are not affected.

You can also deny permissions to an individual user. In the previous example, a nonemployee may have a Windows NT account while working on a short-term project in the database. You can deny the permissions to see CorporateSecrets to their individual user account without creating a SQL Server database role for the purpose.

Note You can deny permissions to user accounts only in the current database, for objects in the current database.

Deactivating Established Access by Revoking Permissions

You can revoke a permission that has been granted or denied previously. Revoking is similar to denying in that both remove a granted permission at the same level. The difference is that while revoking a permission removes a granted permission, it does not prevent the user, group, or role from inheriting a granted permission from a higher level. Therefore, although a user can have permission to view a table directly revoked, it is possible that the user can still view the table because permission to view the table was granted to a role to which they belong.

For example, to remove SELECT access on the Employees table from the HumanResources role, revoke the permission so that HumanResources can no longer use the table. If HumanResources is a member of the Administration role, and you later grant SELECT permission on Employees to Administration, members of HumanResources can see the table through their membership in Administration. If, however, you deny permission to HumanResources, the permission is not inherited if later granted to Administration, because the deny permission cannot be undone by a permission at a different level.

Similarly, it is also possible to remove a previously denied permission by revoking the deny for the permission. However, if a user has other denied permissions at the group or role level, then the user still is denied access.

Note You can revoke permissions to user accounts only in the current database, for objects in the current database.

Permission Conflicts and States

The permissions granted to a group or role are inherited by members of that group or role. Although a user may have permission granted or revoked at one level, conflicting permissions at a higher level, for example due to role membership, can prevent or allow a user access to an object.

Deny 

A denied permission always takes precedence. Denied permission at any level (user, group, or role) denies the permission on the object regardless of existing granted or revoked permissions for that user. For example, if user John, who is a member of the sales role, which is granted SELECT permissions on the customer table, is explicitly denied SELECT permissions on the customer table, he can no longer access it. Similarly, if the sales role is denied access to customer, but John is granted access, he is denied access.

Revoke 

A revoked permission removes only the granted or denied permission at the level revoked (user, group, or role). The same permission granted or denied at another level such as a group or role containing the user, group, or role still applies. For example, if the sales role is granted SELECT permissions on the customer table, and John (a member of sales), is explicitly revoked SELECT permissions on the customer table, he can still access the table because of his membership in the sales role. To prevent John from accessing the customer table, either revoke (assuming no other permissions have been granted elsewhere) or deny permission to the sales role (preventing all members of sales from accessing the table), or explicitly deny John SELECT permissions on customer.

Grant 

A granted permission removes the denied or revoked permission at the level granted (user, group, or role). The same permission denied at another level such as group or role containing the user still applies. However, although the same permission revoked at another level still applies, it does not prevent the user from accessing the object. For example, if John is already explicitly denied access to customer, and sales is revoked access, and John is then explicitly granted access to customer, he can now access customer because the deny is removed. The revoke permission for sales joined with the granted permission for John gives John a granted permission overall.

Therefore, a user receives the union of all the permissions granted, denied, or revoked on an object, with any denied permissions taking precedence over the same permissions granted or revoked at another level.

The following diagram shows how the three permission management activities affect the state of a permission for a user account.

Cc917569.statdiag(en-us,TechNet.10).gif 

Database Access vs. Object Access 

As an example of a permission conflict, a Microsoft Windows NT user LONDON\joe belongs to the LONDON\clerks and LONDON\secretaries Windows NT groups. LONDON\joe can log in to Microsoft SQL Server because the LONDON\clerks group has been granted permissions to connect to SQL Server. Additionally, LONDON\joe can access the secrets database because the LONDON\secretaries group has been granted permissions to access the database.

Note At this point there is no specific entry in the SQL Server system tables, sysusers and syslogins, for LONDON\joe. These system tables contain only entries for the LONDON\clerks and LONDON\secretaries groups.

LONDON\joe creates a table, joetable, in the secrets database. At this point, a new entry is created in the sysusers table for LONDON\joe specifying him as the object owner but not granting him database access. If LONDON\joe is dropped from the LONDON\secretaries group, he can no longer access the secrets database, although he owns an object, joetable, in the database.

See Also 

In This Volume 

Adding a Windows NT User or Group

Understanding Complex Permission Chains

Views and stored procedures provide a secondary method of giving users access to data and the ability to perform activities. They provide users with access to underlying items in the database and bypass the permissions defined directly for specific objects and statements.

Views as Security Mechanisms

Views can serve as security mechanisms. Through a view, users can query and modify only the data they can see. The rest of the table or database is neither visible nor accessible. Permission to access the subset of data in a view must be granted, denied, or revoked, regardless of the set of permissions in force on the underlying table(s).

For example, the salary column in a table contains confidential employee information, but the rest of the columns contain information that should be available to all users. You can define a view that includes all of the columns in the table with the exception of the sensitive salary column. As long as table and view have the same owner, granting SELECT permissions on the view allows the user to see nonconfidential columns in the view without having any permissions on the table itself.

By defining different views and selectively granting permissions on them, users, groups, or roles can be restricted to different subsets of data. The following examples illustrate the use of views for security purposes:

  • Access can be restricted to a subset of the rows of a base table. For example, you might define a view that contains only rows for business and psychology books and keep information about other types of books hidden from users. 

  • Access can be restricted to a subset of the columns of a base table. For example, you might define a view that contains all the rows of the titles table but omits the royalty and advance columns because this information is sensitive. 

  • Access can be restricted to a row-and-column subset of a base table. 

  • Access can be restricted to the rows that qualify for a join of more than one base table. For example, you might define a view that joins the titles, authors, and titleauthor tables to display the names of authors and books they have written. This view hides personal data about the authors, and financial information about the books. 

  • Access can be restricted to a statistical summary of data in a base table. For example, you might define a view that contains only the average price of each type of book. 

  • Access can be restricted to a subset of another view or of some combination of views and base tables. 

Permissions and ALTER VIEW 

The ALTER VIEW Transact-SQL statement can be used to change the definition of a view without having to drop the view and reapply permissions. Any permissions applied to a column in the view are based on the column name defined in the view, rather than the underlying column in the table. Therefore, changing the definition of the view with ALTER VIEW, using the same column name, but a different underlying column in a table, results in the same permissions for the new column. For example, assuming the user Fred exists in the pubs database:

USE pubs
GO
CREATE VIEW v1 AS SELECT title_id, title FROM titles
GO
GRANT SELECT(title_id) ON v1 TO Fred
GO
ALTER VIEW v1 AS SELECT qty AS 'title_id' FROM sales
GO

Although the view is altered so that the title_id column name refers to the qty column in the sales table, rather than the title_id column in the titles table, the SELECT permissions granted to Fred on the title_id column name still apply.

See Also 

In Other Volumes 

"ALTER VIEW" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"CREATE VIEW" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Stored Procedures as Security Mechanisms

Stored procedures can be used to customize security permissions in much the same way as views and are commonly used as an easy interface to perform complex activities. In an archiving scenario, data older than a specified interval is copied into an archive table and then deleted from the primary table. Permissions can be used to prevent users from deleting the rows from the primary table directly or from inserting rows into the archive table without deleting them from the primary table. You can create a procedure to ensure that both of these activities are performed together, and then grant users permissions to execute the procedure.

See Also 

In Other Volumes 

"CREATE PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Ownership Chains

Views can depend on other views or tables. Procedures can depend on other procedures, views, or tables. These dependencies can be thought of as an ownership chain.

Typically, the owner of a view also owns the underlying objects (other views or tables), and the owner of a stored procedure often owns all the procedures, tables, and views referenced. Also, views and underlying objects are usually all in the same database, as are stored procedures and all the objects referenced.

When a user accesses a view, Microsoft SQL Server does not check permissions on any of the view's underlying objects if these objects and the view are all owned by the same user, and if the view and all its underlying objects are in the same database. If the same user owns a stored procedure and all the views or tables it references, and if the procedure and objects are all in the same database, SQL Server checks only the permissions on the procedure.

If the ownership chain of a procedure or view is broken (not all the objects in the chain are owned by the same user), SQL Server checks permissions on each object in the chain whose next lower link is owned by a different user. In this way, SQL Server allows the owner of the original data to retain control over who is authorized to access it.

Ordinarily, a user who creates a view has to grant permissions only on that view. For example, Mary has created a view called auview1 on the authors table, which she also owns. If Mary grants Sue permission to use auview1, SQL Server allows Sue access to it without checking permissions on authors.

A user who creates a view or stored procedure that depends on an object owned by another user must be aware that any permissions he or she grants depend on the permissions allowed by the other owner.

For example, Joe creates a procedure called procedure1, which depends on procedure2 (also owned by Joe), and procedure3 (owned by Sue). These procedures in turn depend on other tables and views owned by Joe and Sue.

Cc917569.ownerchr(en-us,TechNet.10).gif

Joe grants Mary permission to use procedure1. SQL Server checks the permissions on procedure1, procedure3, view2, table2, and table3 to check that Mary is allowed to use them.

Planning Security

The purpose of a security plan is to identify which users can see what data and perform what activities in the database. The scenarios covered in this topic follow these steps in presenting security requirements:

  1. List all the items and activities in the database that must be controlled through security. 

  2. Identify the individuals and groups in the company. 

  3. Cross-reference the two lists to identify which users can see what data and perform what activities in the database. 

Security Examples 

The following security examples are presented to display three common security designs ranging from the simplest single user database to a large corporate security system. Each example contains an explanation of the planning and implementation steps to create a Microsoft SQL Server security system for the specific example.

Single Person Security Example

In this example, a single person is responsible for all aspects of the database and will be the only person using it. The user must be able to create the database and its tables, write programs that interface with the data, load and maintain data, and produce reports.

This scenario presents the simplest possible security system. There is only a single user who needs full access to all activities and data in the database.

User account

Activity

LONDON\tombrown

All database access

The first step is to add a Microsoft SQL Server user account for LONDON\tombrown. This user account allows access to SQL Server. The predefined sysadmin role contains all permissions necessary for this user, so the LONDON\tombrown SQL Server user account should be added as a member of sysadmin. When LONDON\tombrown connects to SQL Server, SQL Server trusts that he has been authenticated by Microsoft Windows NT, so his connection is accepted, and he is allowed to perform activities based on the permissions associated with sysadmin.

If SQL Server is running under Mixed Mode, a user account tombro could be added to SQL Server independent of the Windows NT user account, and tombro could then be added to sysadmin. When the user logs into Windows NT and attempts to connect with SQL Server, he must specify the tombro user account name and password.

Small Company Security Example

In this example, there are people who perform a few different tasks within the database. The database administrator is responsible for the database environment: creating the database, tables, and security accounts, performing backups, and tuning the database. Two developers are responsible for writing client applications to provide an interface to the data. The managers in the company need to view all of the data and prepare information reports. The administrative staff performs data entry and must be able to view customer and sales data.

The users-to-activity map for this example is slightly more complicated than a single user database.

User account

Activity

LONDON\joetuck

All database access

LONDON\marysmith, LONDON\billb

Full access to data, create procedures

LONDON\managers

Full access to all data

LONDON\admins

Full access to customer data and sales, read-only for all other data

The first step in installing the security for this example is to add a user account for LONDON\joetuck. Because the LONDON\joetuck user account requires full access, the next step is to add this user to the sysadmin role.

For the developers, accounts must be added to Microsoft SQL Server for LONDON\marysmith and LONDON\billb. Permissions to access the data could be added to both of their user accounts, but if another developer or another ten developers joined the project, the permissions would have to be added to each new person. A better solution is to add a new SQL Server database role named Developers and grant the permissions to access data and create procedures. When LONDON\marysmith and LONDON\billb or accounts for other new developers are added to the Developers role, their user accounts get the permissions granted to the role.

Accounts must have to be added to SQL Server for LONDON\managers and LONDON\admins. The permissions should be added directly to SQL Server, so there is no need to add new SQL Server database roles. When a manager connects, they are recognized as a member of an existing Microsoft Windows NT group and allowed to connect and perform activities based on the permissions granted to their Windows NT group. The same is true for LONDON\admins.

Corporate Environment Security Example

The security systems in central databases used by large corporations are nearly as complex as the organizational structure of the large companies themselves. Corporations have large numbers of users who perform specialized, exclusive tasks.

In this example, there is a single person is responsible for all aspects of the database application. A few people are responsible for creating databases and tables, but they must not be allowed to see sensitive personnel information about their coworkers (or even themselves). There is an evening team that backs up data, but these workers need not see the data, or create tables and databases. The personnel department must have access to general employee information, and a few select individuals in Personnel will be the only people in the company with access to confidential and sensitive employee information. There are also customer service employees who will need to see but not change product specifications in response to customer inquiries.

The users-to-activity map for this example is fairly complex.

User account

Activity

LONDON\annej

All database access

LONDON\dbadmins

Create databases

LONDON\dboperations

Perform evening backups

LONDON\personnel

Full access to general employee data

LONDON\mikebo, LONDON\marym, LONDON\billsm

Full access to confidential data

LONDON\custservice

Read-only access to product information

As in the previous example, the LONDON\annej user account must be created in Microsoft SQL Server and added to the sysadmin role that has full permissions. The LONDON\dbadmins Microsoft Windows NT group user account must be added in SQL Server and granted the permission to create databases. The LONDON\operations Windows NT group should be added also and granted only the BACKUP DATABASE permissions to allow them to perform backups.

The LONDON\personnel Windows NT group should be added and granted the permissions to see only the nonsensitive columns in the employees table, as well as the permissions to see other tables.

The users LONDON\mikebo, LONDON\marym, and LONDON\billsm are members of the LONDON\personnel Windows NT group, so they already have the permissions necessary to do most of their work, but they also need special access to the sensitive employee information columns. To fit this need, a database role called PersonnelSecure could be created in SQL Server and granted the permissions required to see the sensitive employee information. Individual users get the special permissions in SQL Server when added to the role. An alternative method is to add the special permissions to their user accounts directly.

The final step is to add an account for the LONDON\custservice Windows NT group in SQL Server, and grant the permission to see product information.

Advanced Security Topics

The security topics presented here go beyond the basic use of security in Microsoft SQL Server and provide more detail for specialized applications.

Application Security and Application Roles

The security system in Microsoft SQL Server is implemented at the lowest level, the database itself. This is the best, most robust method for controlling user activities regardless of the application used to communicate with SQL Server. However, you may have situations when security controls must be customized to accommodate the special requirements of an individual application, especially when dealing with complex databases and databases with large tables.

Additionally, you may want users to be restricted to accessing data only through a specific application without the ability to access data directly, for example using SQL Server Query Analyzer or Microsoft Excel. This prevents a user from connecting to SQL Server using an application such as SQL Server Query Analyzer and executing a poorly written query, which affects the performance of the whole server.

SQL Server accommodates this situation through the use of application roles. The fundamental differences between standard and application roles are:

  • Application roles contain no members. Users, Microsoft Windows NT groups, and roles cannot be added to application roles; the permissions of the application role are gained when the application role is activated for the user's connection through a specific application(s). A user's association with an application role is due to being capable of running an application that activates the role, rather than being a member of the role. 

  • Application roles are inactive by default and require a password to be activated by using the sp_setapprole system stored procedure. The password can be provided by the user, for example, through an application prompt, but it is more likely that the password is incorporated within the application. The password can be encrypted as it is sent to SQL Server. 

  • When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only by virtue of permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs out of SQL Server. 

Important It is necessary for a connection to lose default permissions applied to the login/user account or other groups or database roles in all databases for the duration of the connection and gain the permissions associated with the application role to ensure that all the functions of the application can be performed. For example, if a user is usually denied access to a table that the application must access, then the denied access should be revoked for the user to successfully use the application. Application roles overcome any conflicts with user's default permissions by temporarily suspending the user's default permissions and assigning them only the permissions of the application role.

Application roles allow the application to take over the responsibility of user authentication, rather than SQL Server. However, because SQL Server still needs to authenticate the application when it accesses databases, the application must provide a password because there is no other way to authenticate an application.

If ad hoc access to a database is not required, users and Windows NT groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database. In such an environment, assuming access to the applications is secure, standardizing on one system-wide password assigned to an application role is possible.

There are several options for managing application role passwords without hard-coding them into applications. For example, an encrypted key stored in the registry (or the SQL Server database), for which only the application has the decryption code, can be used. The application reads the key, decrypts it, and uses the value to set the application role. Using the Multiprotocol Net-Library, the network packet containing the password can also be encrypted. Additionally, the password can be encrypted, before being sent to SQL Server, when the role is activated.

When an application user connects to SQL Server using Windows NT Authentication Mode, an application role is a good way to set the permissions the Windows NT user has in a database when using the application. This allows Windows NT auditing of the user account and control over user permissions, while they use the application, to be easily maintained.

If SQL Server Authentication is used and auditing user access in the database is not required, it can be easier for the application to connect to SQL Server using a predefined SQL Server login. For example, an order entry application authenticates users running the application itself, and then connects to SQL Server using the same OrderEntry login. All connections use the same login, and relevant permissions are granted to this login.

Note Application roles work with both authentication modes.

Example 

As an example of application role usage, a user Sue runs a sales application that requires SELECT, UPDATE, and INSERT permissions on the Products and Orders tables in database Sales to work, but she should not have any SELECT, INSERT, or UPDATE permissions when accessing the Products or Orders tables using SQL Server Query Analyzer or any other tool. To ensure this, you could create one user-database role that denies SELECT, INSERT, or UPDATE permissions on the Products and Orders tables, and add Sue as a member of that database role. You can then create an application role in the Sales database with SELECT, INSERT, and UPDATE permissions on the Products and Orders tables. When the application runs, it provides the password to activate the application role by using sp_setapprole, and gains the permissions to access the Products and Orders tables. If Sue tries to log in to SQL Server using any tool except the application, she will not be able to access the Products or Orders tables.

Allowing Other Accounts to Grant Object Permissions

When you grant an object permission to a user account (Microsoft Windows NT user or group, or Microsoft SQL Server user or role) in a database, you can optionally specify the WITH GRANT OPTION clause, giving the ability to grant that object permission to others. For example, if you use the WITH GRANT OPTION clause when you grant permissions on the salaries table to the user user_a, user_a is able to grant the same permissions on the table to any other user account in the database. For groups and roles, if you grant permissions on the salaries table to role role_a specifying the WITH GRANT OPTION clause, each member of role_a can grant the object permission to any other user account, provided that the AS clause of the GRANT statement is specified. For more information, see "GRANT" in Microsoft SQL Server Transact-SQL and Utilities Reference.

Note You should be careful using the WITH GRANT OPTION clause as you have no future control over which security accounts will receive that permission.

When you revoke a permission granted using the WITH GRANT OPTION clause, specify the CASCADE clause to have the permissions revoked from the user account as well as any other accounts that received the permission from the initial account.

For example, you have granted a permission specifying WITH GRANT OPTION to the user user_a. User_a granted the permission specifying WITH GRANT OPTION to the user user_b, and user_b granted the permission to the user user_c. User_a has left the company, but SQL Server does not allow you to remove a user account if it has granted a permission specifying the WITH GRANT OPTION clause to another account. Specifying the WITH GRANT OPTION clause has created a chain from user_a through user_b to user_c. SQL Server will not let you remove the account for user_a until the permissions are revoked for user_b and user_c. When you revoke the permission from user_a and specify the CASCADE option, the permission is removed from the user_a, user_b, and user_c accounts. You may then remove the user_a account.

Encryption

Encryption is a method for keeping sensitive information confidential by changing data into an unreadable form. Encryption ensures that data remains secure by keeping the information hidden from everyone, even if the encrypted data is viewed directly. Decryption is the process of changing encrypted data back into its original form so it can be viewed by authorized users.

Microsoft SQL Server encrypts or can encrypt:

  • Login and application role passwords stored in SQL Server. 

  • Any data sent between the client and the server as network packets. 

  • Stored procedure definitions. 

  • View definitions. 

  • Trigger definitions. 

Login and Application Role Passwords 

Login and application role passwords stored in the SQL Server system tables are always encrypted. This prevents any user, including system administrators, from viewing any password, including their own. Additionally, application role passwords can be encrypted, before they are sent over the network, when the application role is activated.

Note Using the sp_addlogin system stored procedure, SQL Server logins can be added without encrypting the password, if required. However, this is not recommended unless the passwords are already encrypted.

Data in Network Packets 

SQL Server allows data sent between the client and the server to be encrypted. This ensures that any application or user intercepting the data packets on the network cannot view confidential or sensitive data, for example, passwords sent across the network as a user logs into SQL Server, or personnel data containing salary information.

Encryption is implemented and configured by using the SQL Server Multiprotocol Net-Library.

Stored Procedure, View, and Trigger Definitions 

When the definition of a stored procedure, trigger, or view is saved in the syscomments system table, it can be encrypted optionally. Encrypting these definitions can be useful if, for example, a SQL Server system contains proprietary stored procedures, triggers, or views whose definitions should not be viewed by users and third parties.

See Also 

In This Volume 

Multiprotocol Clients

In Other Volumes 

"CREATE PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"CREATE TRIGGER" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"CREATE VIEW" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"sp_addlogin" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"sp_setapprole" in Microsoft SQL Server Transact-SQL and Utilities Reference 

SQL Server File Permissions

Microsoft SQL Server must create and access files to store databases, database backups, error logs, and so on. The SQL Server process must run in the context of a security account that has the necessary permissions to create and access these files, whether these files exist on the local computer or a network drive on a remote computer. The security account SQL Server uses depends on the method used to start SQL Server. If SQL Server is started:

  • As a service on Microsoft Windows NT using the Service Control Manager, SQL Server uses the security account assigned to the SQL Server service (in Windows NT Control Panel). 

  • At the command prompt, independent of Service Control Manager, SQL Server uses the security account of the logged on user. 

  • In Microsoft Windows 95/98, SQL Server uses the security account of the logged on user. 

The security account used by SQL Server requires full access permissions to the file system to create, read, write, delete, and execute files. For example, using the Windows NTFS file system, the security account used by SQL Server requires authority to create files with NTFS Full Control permission.

To prevent unauthorized access to the files used by SQL Server, you can adjust the permissions on the files directly to allow only the security account used by SQL Server access to the files.

Note If SQL Server uses the Windows NT LocalSystem built-in security account, file permissions must be granted to the SYSTEM account of the local computer running SQL Server.

Securing the Windows NT Registry 

It is recommended that write permissions for users who are not SQL Server system administrators be removed from the KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer key in the Windows NT registry.

For more information about adjusting permissions on files and the Windows NT registry, see your Windows NT documentation.

See Also 

In This Volume 

Starting SQL Server

In Other Volumes 

"Creating SQL Server Services User Accounts" in Microsoft SQL Server Introduction 

Hiding or Revealing SQL Server on a Network

When you install Microsoft SQL Server, SQL Server Setup makes an entry in the Microsoft Windows NT Registry that enables Named Pipes clients to see SQL Server in a server enumeration box in SQL Server Query Analyzer.

For security purposes, you can hide a server on the network. Named Pipes clients can still connect to it, but they cannot see the hidden server when viewing servers. You can reveal the server at any time.

Scripting Data Access Controls in Internet Explorer

Microsoft SQL Server version 7.0 ships with several data access controls:

  • SQL Namespace (SQL-NS) 

  • SQL Distribution control (replication) 

  • SQL Merge control (replication) 

These controls are signed and marked "safe for initialization and scripting" and can be used in Microsoft Internet Explorer version 4.0 or later.

Before deploying controls that can connect to data sources, you should thoroughly understand the security implications of opening this access. When you use any of the SQL Server controls, the primary security concern is the ability to run under the authorized user's account through a Windows NT Authentication login to SQL Server. A Web page with a scripted control runs with the network identity of the user browsing the page. If the data source connection is based on the connected user's network identity (using Windows NT Authentication login to SQL Server), the control can access any data that the user browsing the page can access. If a malicious Web page using the control is sent to a user, the control has the permissions of the user browsing the Web page. The control can then read or make changes to databases without the user's knowledge.

To prevent unauthorized access or changes to a database, all the data access controls shipped with SQL Server 7.0 that are marked as "safe for scripting" take into account security zones settings when being loaded in Internet Explorer 4.0 or later. (If a control is not marked safe for scripting, it can run a script inside of Internet Explorer only at the Low security mode of Internet Explorer, and even then only after the user responded to a message stating that a script will be run.) Another way to deal with the issue is to remove the user's ability to do an Windows NT Authenticated login to SQL Server.

Internet Explorer 4.0 does not provide an explicit security option for data access. Therefore, all the controls marked safe for scripting allow, prompt, or disallow scripting based on the security zone being used. The table shows the Internet Explorer 4.0 settings.

Security zone

Internet Explorer 4.0 notification

(local machine zone)

Controls can be initialized or scripted regardless of data source or scripts.

Local intranet zone

User is warned of potential safety violation prior to loading the page. User can accept or reject initialization or scripting.

Trusted sites zone

Controls can be initialized or scripted regardless of data source or scripts.

Internet zone

User is warned of potential safety violation prior to loading the page. User can accept or reject initialization or scripting.

Restricted sites zone

Scripting errors occur if user attempts to view page and execute script.

In contrast to Internet Explorer 4.0, Internet Explorer version 5.0 supports an explicit security option for data access called "Access data sources across domains." This option can be customized, and the setting of this action is used to determine how the controls behave when they are run in Internet Explorer 5.0. The default settings in Internet Explorer 5.0 are the same as the programmed settings in Internet Explorer 4.0.

As with all security concerns, you must take specific actions to safeguard your system. The capabilities described above provide the mechanics to do so. SQL Server is protected from security attacks only if users with the ability to do Windows NT Authenticated logins to SQL Server configure the security settings correctly, and that they answer all security prompts correctly. Users with access to secure data sources should be educated about the potential security risks and how to configure the Internet Explorer security settings and respond to data access prompts.

Note These general steps to safeguard your system apply to any scripting host, including Microsoft Excel spreadsheets or Microsoft Word documents. Users that have the ability to Windows Authenticated logins should always enable the macro warning feature or similar security setting of an application to detect and actively prevent any malicious attacks on your data.

See Also 

In Other Volumes 

"Developing SQL-DMO Applications" in Microsoft SQL Server Distributed Management Objects 

"Programming SQL-NS Applications" in Microsoft SQL Server Building Applications 

"Creating DTS ActiveX Scripts" in Microsoft SQL Server Building Applications 

"Programming Replication ActiveX Controls" in Microsoft SQL Server Building Applications 

Cc917569.spacer(en-us,TechNet.10).gif