SQL Server 2000 Security Model

Article
01/28/2010

Updated : May 16, 2003

Introduction

This paper introduces Microsoft® SQL Server™ administrators and developers to the security features of SQL Server 2000 Service Pack 3 (SP3). It discusses security best practices, giving detailed recommendations, and includes source code examples. It concludes with a security best practices checklist. This paper focuses on the SQL Server engine. For detailed discussions of Replication, Analysis Services, or Data Transformation Services, see SQL Server 2000 Books Online (Updated - Service Pack 3).

Assumptions

SQL Server 2000 security is based on the Microsoft Windows NT® 4.0 and Windows 2000 security model; therefore, a basic understanding of Windows NT 4.0 and Windows 2000 security is assumed throughout this paper. An understanding of the concept of domains, global groups, local groups, and user accounts as they apply in the context of Windows NT 4.0 security, as well as Microsoft Active Directory® in Windows 2000, is also assumed.

For those interested in the code examples, exposure to Microsoft Visual Basic® and Structured Query Language (SQL) will enhance understanding. In addition, experience using SQL Distributed Management Objects (SQL-DMO) provides a significant advantage.

Even those who have had limited exposure to Windows NT 4.0, Windows 2000, or SQL Server may, however, gain significant understanding about security when using these products.

SQL Server 2000 Security Model

The SQL Server 2000 security model is based on the Windows security model. For detailed discussion of Windows security best practices, see Windows 2000 Server Security Center.

SQL Server 2000 should be secured as outlined in the following diagram (Figure 1).

Figure 1: Windows users and groups offer a powerful and flexible security model for SQL Server administrators

The steps in the diagram can be summarized as follows:

Users in each domain are assigned to Windows global groups.
The Windows global groups from the various domains are placed into a Windows local group.
The Windows local group is granted rights to log in to SQL Server 2000.
The Windows local group is granted access rights to the appropriate databases. This Windows local group may not be the same one as was used for granting login rights in Step 3. Therefore, Steps 1 and 2 are often repeated to group the users by access permissions required.
The Windows local group is assigned permissions on the specific database objects.

The other approach to security is based on the use of roles, and is usually implemented in the following way (Figure 2).

Figure 2: Role-based security is another security option in SQL Server 2000

When using roles to assign object permissions, individuals must still be granted permissions on the server and the database using the recommended approach.

Steps 1 through 4 are the same for both diagrams, except that multiple Windows global and local groups would probably not be created. Windows 2000 Universal groups are fully supported as well.

Step 5: Individual Windows accounts and Windows groups are assigned to a role.

Step 6: Object permissions are then assigned to the roles.

Roles reduce the requirements of grouping users within Windows, by grouping the users within SQL Server 2000.

Authentication Modes

SQL Server 2000 provides two authentication modes for securing access to the server: Windows Authentication Mode and Mixed Mode.

Windows Authentication Mode

Windows Authentication Mode is the default authentication mode in SQL Server 2000. In Windows Authentication Mode, SQL Server 2000 relies solely on the Windows authentication of the user. Windows users or groups are then granted access to the SQL Server. Connections made to the server using this mode are known as trusted connections.

When Windows Authentication Mode is used, the database administrator allows users to access the computer running SQL Server by granting them the right to log in to SQL Server 2000. Windows security identifiers (SIDs) are used to track Windows authenticated logons. As Windows SIDs are used, the database administrator can grant logon access directly to Windows users or groups.

Mixed Mode

In Mixed Mode, users can be authenticated by Windows Authentication or by SQL Server Authentication. Users who are authenticated by SQL Server have their username and password pairs maintained within SQL Server.

Just like in Windows Authentication Mode, SQL Server connecting in Mixed Mode relies on Windows to authenticate users if the client and server are capable of using NTLM, or Kerberos logon authentication protocols. If the client is unable to use a standard Windows logon, SQL Server requires a username and password pair, and compares this pair against those stored in its system tables. Connections that rely on username and password pairs are called non-trusted connections, or SQL connections.

Using Security Identification Numbers Internally

SQL Server tracks trusted logins using SIDs. Windows users and groups can be granted access to databases or specific database objects directly. For example, Jane is a member of the SALES and MARKETING groups in Windows. The SALES group has been granted permission to log in to SQL Server, and also to access the pubs database. An administrator could grant access to the authors table for Jane by her Windows name, REDMOND\Jane. The Windows account must be referenced by domain and username. In this case, Jane's SID would be stored in the system tables of the pubs database. SQL Server 2000 does not support User Principal Names (UPNs). For example, if my Windows login is domain SALES, user SOMEONE, the login to SQL Server would be SALES\SOMEONE, and you could not use the login in the form of SOMEONE@MYCOMPANY.COM as supported by Windows 2000 Active Directory.

Roles

Roles are used much like Windows groups are used. Roles allow users to be collected into a single unit against which permissions can be applied. Permissions granted, denied, or revoked from a role also apply to any members of the role. Roles can represent a job performed by a class of workers in an organization. Permissions can then be granted to that role. As workers rotate into the job, they are made members of the role; as they rotate out of the job, they are removed. This removes the requirement to repeatedly grant, deny, and revoke permissions to or from individuals as they accept or leave a job.

There are a number of key concepts that make roles so powerful. First, with the exception of fixed server roles, they are implemented within a database. This means that the database administrator does not depend on the Windows administrator for the grouping of users. Second, roles can be nested. There is no restriction on the depth of nesting, but circular nesting is not permitted. Third, a database user can be a member of more than one role simultaneously.

This allows a database administrator to arrange permissions in hierarchies that reflect the administrative structure of the organization that uses the database. For example, a Finance Department might contain separate groups responsible for accounts payable and accounts receivable. The database administrator could create separate database roles for APEmployees and AREmployees, assigning to each role only those permissions needed by employees in the corresponding job. The database administrator could then create a role FinManagers, containing both of the more limited roles (APEmployees, and AREmployees). FinManagers would thus have all the permissions of employees who report through them. When an employee is promoted from accounts payable to management, the database administrator need only add her to the FinManagers role.

Public Role

The public role exists in every database, including the system databases master, msdb, tempdb and model. The public role provides the default permissions for users in a database and cannot be deleted. Functionally, it can be compared to the Everyone group in the Windows NT 4.0 environment. Every database user is a member of this role automatically; therefore, users cannot be added or removed from this role.

Predefined Roles

SQL Server 2000 includes several predefined roles. These roles have predefined implied permissions, which cannot be granted to other user accounts. There are two types of predefined roles: fixed server roles and fixed database roles.

Fixed Server Roles

Fixed server roles are server-wide in their scope. They exist outside of the databases. Each member of a fixed server role is able to add other logins to that same role.

Note: All members of the Windows BUILTIN\Administrators group (the local administrator's group) are members of the sysadmin role by default.

The following table (Table 1) lists the fixed server roles found in SQL Server 2000.

Table 1: SQL Server 2000 fixed server roles

Fixed Server Role	Description
Sysadmin	Performs any activity in SQL Server.
Serveradmin	Configures server-wide configuration options, shuts down the server.
Setupadmin	Adds and removes linked servers, and executes some system stored procedures, such as sp_serveroption.
securityadmin	Manages server-wide security settings, including linked servers, and CREATE DATABASE permissions. Resets passwords for SQL Server authentication logins.
processadmin	Terminates processes running in SQL Server.
dbcreator	Creates, alters, drops, and restores any database.
diskadmin	Manages disk files.
Bulkadmin	Allows a non-sysadmin user to run the bulkadmin statement.

To add users to the fixed server roles, use the following Transact-SQL statement:

/* Add Bob to the sysadmin server role */
exec sp_addsrvrolemember "REDMOND\Bob", "sysadmin"

Windows users and groups can be added to server roles. The following code shows how a user is added to a server role using the SQL Distributed Management Objects (DMO) collection:

' Declare variables
Dim oServer As SQLDMO.SQLServer
' Create a server object and connect
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Add Bob to the sysadmin server role
oServer.ServerRoles("sysadmin").AddMember ("REDMOND\Bob")

For more information about the use of fixed server roles, see SQL Server Books Online.

Fixed Database Roles

Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_security administrator roles can manage fixed database role membership; however, only the db_owner role can add others to the db_owner fixed database role.

The following table (Table 2) lists the fixed database roles found in SQL Server 2000.

Table 2 SQL Server 2000 fixed database roles

Fixed database role	Description
db_owner	Performs all maintenance and configuration activities in the database.
db_accessadmin	Adds or removes access for Windows users, groups, and SQL Server logins.
db_datareader	Reads all data from all user tables.
db_datawriter	Adds, deletes, or changes data in all user tables.
db_ddladmin	Runs any Data Definition Language (DDL) command in a database.
db_securityadmin	Modifies role membership and manages permissions.
db_backupoperator	Backs up the database.
db_denydatareader	Cannot read any data in user tables within a database.
db_denydatawriter	Cannot add, modify, or delete data in any user tables or views.

For more information about the use of fixed database roles, see SQL Server Books Online.

User-Defined Roles

User-defined roles provide an easy way to manage permissions in a database when a group of users performs a specified set of activities in SQL Server 2000. In situations in which there is no applicable Windows group, or the database administrator does not have permissions to manage the Windows user accounts, user-defined roles provide the database administrator the same flexibility as Windows groups.

User-defined roles apply only at the database level, and are local to the database in which they were created.

Application Roles

Application roles allow a database administrator to grant access to data only to those users who are using a particular application.

The process works as follows. A user connects to a database through an application. The application then proves its identity to SQL Server by executing the sp_setapprole stored procedure, which takes two parameters: application role name and password. (The application role password is known only to the application.) If the application role name and password are valid, the application role is activated. At this point all the permissions currently assigned to the user are dropped, and the security context of the application role is assumed. Since only the application (not the user) knows the password for the application role, only the application can activate this role and access objects to which the role has permissions.

After application roles are activated, they cannot be deactivated. The only way for a user to re-acquire its original security context is to disconnect and reconnect to SQL Server.

Application roles work with both authentication modes, and contain no members. Users cannot be associated with application roles, as the application requests the application role's security context using the sp_setapprole stored procedure.

Similar to user-defined roles, application roles exist only within a database. If an application role attempts to access another database, that application role will only be granted the privileges of the guest account in that database. If the guest account has not been specifically granted access to the data, or does not exist, the objects cannot be accessed.

Another key concept in the use of application roles is that the user who is running the application is audited within SQL Server 2000. In other words, application roles provide the security context within which the database object permissions are checked, but the identity of the actual user is not lost.

Here is an example of an implementation using application roles. If Jane is a member of the ACCOUNTING group, and the ACCOUNTING group members are permitted to gain access only to the data in SQL Server through the accounting software package, an application role could be created for the accounting software. The ACCOUNTING application role would be granted access to the data, while the ACCOUNTING Windows group would be denied access to the data. Thus, when Jane attempts to access the data using SQL Query Analyzer, she will be denied access; but when Jane uses the accounting software, she will be able to access the data.

This procedure outlines how an application can make use of application roles. To use application roles:

Create an application role.
Assign permissions to the application role.
Ensure that the end user connects to the server through the application.
Ensure the client application activates the application role.

The first two steps of this process are usually separated from the last two steps. Therefore, two code fragments will follow for Transact-SQL and Microsoft Visual Basic®, respectively.

The Transact-SQL script is as follows:

/* Create the application role. */
EXEC sp_addapprole "AccAppRole", "ABC"
/* Grant permissions to SELECT. */
GRANT SELECT
ON authors
TO AccAppRole
GO

Here is the code to activate the role:

/* Activate the role. */
EXEC sp_setapprole "AccAppRole", {ENCRYPT N "ABC"}

The encryption of the password is optional, but ensures greater security when the password has to pass through a wide area network (WAN).

Here is the code in Visual Basic:

' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oDbRole As SQLDMO.DatabaseRole
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Create the Role object.
Set oDbRole = CreateObject("SQLDMO.DatabaseRole")
' Set the appropriate properties.
oDbRole.Name = "AccAppRole"
oDbRole.AppRole = True
oDbRole.Password = "45$#Jxew&fd2$Dw53987"
' Add the Role object to the servers Role collection.
oServer.Databases("pubs").DatabaseRoles.Add oDbRole

To use the role:

' Declare variables.
Dim oConnection As ADODB.Connection
' Create the connection object and connect.
Set oConnection = CreateObject("ADODB.Connection")
oConnection.Provider = "sqloledb"
oConnection.Open "Server=SERVERNAME;Database=pubs;Trusted_Connection=yes"
' Activate the application role. There is no error handling for this sample.
oConnection.Execute "EXEC sp_setapprole 'AccAppRole', {ENCRYPT N '45$#Jxew&fd2$Dw53987'}, 'ODBC'"

The encryption style (last parameter) must be set for OLE DB and ODBC data sources. No other data sources can explicitly encrypt the password. In these cases, you must use an encrypted communications protocol with the server. See the discussions of SSL and IPSec later in this paper.

Application roles are implemented per session. If your application opens multiple sessions and all sessions are required to use the same role, each session must first activate the role.

Implementing application roles can be used to provide much more granular security than ever before. For example, a client application could use the user's security context on some connections, while using an application role on another.

When using application roles, executing SELECT USER returns the name of the application role currently being used. If the identity of the logged-on user is required, use the following Transact-SQL statement: SELECT SYSTEM_USER.

Note: Using application roles requires storing passwords. Be sure to use appropriate encryption and ACLs. See the topic "Storing Credentials" later in this paper.

Securing Access to the Server

Access to the server is controlled differently by the two authentication modes in SQL Server 2000. However, after a user gains access to the server, the authentication modes are identical. SQL Server 2000 security defaults to Windows Authentication when it is installed.

Windows Level

When securing access at the Windows level, administrators should create a Windows domain login account for each user who will be accessing SQL Server (if the user does not already have an account).

At the Windows domain level, create global groups with permissions appropriate to specific job functions in your organization. Add Windows users to the appropriate global groups. On the computer running SQL Server 2000, create Windows local groups with permissions appropriate to the kinds of work users will do on SQL Server. Finally, add the appropriate Windows global groups to the Windows local groups.

The goal of this process is to gather rights and users into groups that can be managed collectively. Although the preliminary process may appear excessively complex, it is highly recommended. An initial investment in careful security planning will yield compound interest in the form of improved system security and ease of administration in the future.

For a detailed discussion of Windows-level security, see Microsoft Solution for Securing Windows 2000 Server.

Securing Access to the Database

Successful login does not automatically allow a user access to all databases on SQL Server 2000. Permissions must be granted to allow users to access a database.

In this section, we do not differentiate between non-trusted users, Windows users, and Windows groups. When reference is made to Windows users or groups, they can also be users or global groups in trusted domains, or domains within the same tree or forest.

Within each database, a user is created and is linked to a SQL Server login, a Windows user, or a Windows group.

SQL Server Enterprise Manager (which is a Microsoft Management Console [MMC] snap-in for administering SQL Server 2000) does not allow the creation of users who do not have specific logon permissions. The MMC creates a list of all accounts that have been granted the permission to log on to the server, and a selection needs to be made from this list. The same applies to the SQL-DMO object model.

With Transact-SQL, any valid SQL Server logon, Windows user, or Windows group can be granted the rights to access the database, whether or not a specific logon exists in the sysxlogins table in the master database.

Note: Although not a technical requirement, if you are using trusted connections, it is strongly recommended that you create users with the same username in each database as the logon name.

Some examples for the Transact-SQL statements required to grant permission to use a database are:

/* Grant access to Bob. */
exec sp_grantdbaccess 'REDMOND\Bob'
/* Grant access to Wendy, referring to her by first name within this database. */
exec sp_grantdbaccess 'REDMOND\WendyH', 'Wendy'

Only one modification would be required to make this example work with non-trusted clients. Instead of the domain username, use the username that SQL Server 2000 uses to authenticate the user.

Using SQL-DMO, the equivalent functionality would be achieved by the following code:

' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oUser As SQLDMO.User
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Create the User object.
Set oUser = CreateObject("SQLDMO.User")
' Set the appropriate properties.
oUser.Name = "Bob"
oUser.Login = "REDMOND\Bob"
' Add the User object to the servers Users collection.
oServer.Databases("pubs").Users.Add oUser

SQL Server Level

At the SQL Server 2000 level, you must grant permissions for the created Windows local groups to log in to SQL Server. You can also grant permission to log in to SQL Server directly to users, but is not as practical to administer except for the smallest of environments. Permissions to log on to the server can be granted with Enterprise Manager or implemented programmatically using Visual Basic or Transact-SQL.

Database administrators working with Transact-SQL should familiarize themselves with the Books Online entries for these security-related stored procedures:

sp_addalias	sp_droprole
sp_addapprole	sp_droprolemember
sp_addgroup	sp_dropserver
sp_addlinkedsrvlogin	sp_dropsrvrolemember
sp_addlogin	sp_dropuser
sp_addremotelogin	sp_grantdbaccess
sp_addrole	sp_grantlogin
sp_addrolemember	sp_helpdbfixedrole
sp_addserver	sp_helpgroup
sp_addsrvrolemember	sp_helplinkedsrvlogin
sp_adduser	sp_helplogins
sp_approlepassword	sp_helpntgroup
sp_change_users_login	sp_helpremotelogin
sp_changedbowner	sp_helprole
sp_changegroup	sp_helprolemember
sp_changeobjectowner	sp_helprotect
sp_dbfixedrolepermission	sp_helpsrvrole
sp_defaultdb	sp_helpsrvrolemember
sp_defaultlanguage	sp_helpuser
sp_denylogin	sp_password
sp_dropalias	sp_remoteoption
sp_dropapprole	sp_revokedbaccess
sp_dropgroup	sp_revokelogin
sp_droplinkedsrvlogin	sp_setapprole
sp_droplogin	sp_srvrolepermission
sp_dropremotelogin	sp_validatelogins

The following Transact-SQL statement grants login rights to the SALESLG local group:

/* Grant login. */
exec sp_grantlogin 'REDMOND\SALESLG'

Alternatively, login rights can be granted with the following Visual Basic code:

' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oLogin As SQLDMO.Login
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Create the Login object.
Set oLogin = CreateObject("SQLDMO.Login")
' Set the appropriate properties.
oLogin.Name = "REDMOND\SALESLG"
oLogin.Type = SQLDMOLogin_NTGroup
' Add the Login object to the server's Logins collection.
oServer.Logins.Add oLogin

To allow a user access to SQL Server 2000 using non-trusted connections, you must create user accounts.

Note: When SQL Server 2000 is installed on Windows and configured to use Mixed Mode, capable clients can still make trusted connections.

The following Transact-SQL script creates a login for a non-trusted connection:

/* Add a login. */
exec sp_addlogin 'Bob', '45$#Jxew&fd2$Dw53987', 'pubs'

This statement adds a user called Bob and sets the password to password. The default database becomes pubs. The default database is the database to which the user is switched when attempting to log in. A user must still create a user account in the default database for this to work; sp_addlogin does not add a user account in the referenced database.

Alternatively, the above can also be achieved using Visual Basic:

' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oLogin As SQLDMO.Login
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Create the Login object.
Set oLogin = CreateObject("SQLDMO.Login")
' Set the appropriate properties.
oLogin.Name = "Bob"
oLogin.Type = SQLDMOLogin_Standard
oLogin.SetPassword "","45$#Jxew&fd2$Dw53987"
' Add the Login object to the server's Logins collection.
oServer.Logins.Add oLogin

Securing Access to the Database Objects

You can grant permissions to roles and users, and assign the permissions to allow users to execute certain statements and to access certain database objects. Statement permissions restrict who can execute statements such as CREATE DATABASE, CREATE TABLE, or CREATE FUNCTION. Object permissions restrict access to objects such as tables, views, user-defined functions or stored procedures. Object permissions are dependent on the object being referenced. For example, object permission for tables include the SELECT, INSERT, UPDATE, DELETE, and REFERENCES permissions, while the object permissions on a stored procedure include EXECUTE permissions.

User-Defined Database Roles

In an ideal environment, roles would not be necessary. In such an environment, all users would be running SQL Server 2000 on Windows 2000 in Windows Authentication Mode. The database administrator could ask the Windows administrator to place all the users with a specific data access requirement (or role) into one Windows group, and the database administrator would then grant permissions to that Windows group as required. In some environments, however, Windows-level or domain-level management of permissions is not possible, so SQL Server roles are available to group users by their permission requirements.

Any Windows user or group can be assigned to a role, which can then be assigned permissions to database objects the same way as database users are assigned permissions.

Note: User-defined roles can be created only in a database. Fixed server roles and fixed database roles are predefined and cannot be modified.

Roles can be created with the following Transact-SQL code:

/* Add role for Telephone Operators. */
exec sp_addrole "TelephoneOperators"

Alternatively, roles can be created with the following Visual Basic code:

' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oDbRole As SQLDMO.DatabaseRole
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Create the Database Role object.
Set oDbRole = CreateObject("SQLDMO.DatabaseRole")
' Set the appropriate properties.
oDbRole.Name = "TelephoneOperators"
' Add the Role object to the servers Role collection.
oServer.Databases("pubs").DatabaseRoles.Add oDbRole

After a user-defined database role is created, users, groups, or other roles are added to it. Roles can be nested, although not in a circular manner, as this would not be productive.

This sample Transact-SQL code adds a Windows user, a Windows group, and a database role to the newly created role:

/* Add a Windows user to the TelephoneOperators role. */
exec sp_addrolemember "TelephoneOperators", "REDMOND\Bob"
/* Add a Windows group to the TelephoneOperators role. */
exec sp_addrolemember "TelephoneOperators", "REDMOND\Sales"
/* Add HelpDeskOperators role to TelephoneOperators role. */
exec sp_addrolemember "TelephoneOperators", "HelpDeskOperators"

And again with SQL-DMO:

' Declare variables.
Dim oServer As SQLDMO.SQLServer
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("MSNZBENTHOM")
' Use with statement for code legibility.
With oServer.Databases("pubs").DatabaseRoles("TelephoneOperators")
' Add the Windows user to the TelehoneOperators role collection.
.AddMember ("REDMOND\Bob")
' Add the Windows group to the TelehoneOperator's role collection
.AddMember ("REDMOND \Sales")
' Add the HelpDeskOperators role to TelehoneOperators role collection.
.AddMember ("HelpDeskOperators")
End With

Permissions System

The permissions system in SQL Server 2000 is based on the same additive model that forms the basis of Windows permissions. If a user is a member of the sales, marketing, and research roles (multiple group memberships), the user gets the sum of the respective permissions of each role. For example, if sales has SELECT permissions on a table, marketing has INSERT permissions, and research has UPDATE permissions, the user would be able to SELECT, INSERT, and UPDATE. However, as with Windows, if a particular role of which the user is a member has been denied a specific object permission (such as SELECT), the user is unable to exercise that permission. The most restrictive permission (DENY) takes precedence.

Granting and Denying Permissions to Users and Roles

Permissions within a database are always granted to database users, roles, and Windows users or groups, but never to SQL Server 2000 logons. The methods used to set the appropriate permissions for users or roles within a database are: granting permissions, denying permissions, and revoking permissions.

The DENY permission allows an administrator to deny an object or statement permission to a user or role. As with Windows permissions, DENY takes precedence over all other permissions.

For example, if some database users are frivolously changing data, it would not be fair to remove permissions from all users, as the majority of the users are using the data responsibly. It is possible to create a new role with a name like trouble_makers, and then DENY the INSERT, UPDATE, and DELETE operations on all tables for this role. As users misbehave, they are put into the trouble_makers role without regard for their other personal, group, or role permissions.

When permissions associated with an object are evaluated, the first step is a check for a DENY permission. If permission is denied, evaluation stops and access is not granted. If DENY is not present, the next step is to compare permissions associated with the object to those of the calling user or process. In this step, the GRANT permission or the REVOKE permission may appear. If permission is granted, evaluation stops and access is granted. When permission is revoked, previous GRANT or DENY permissions are deleted. Revoking permissions is thus not the same as denying permissions. The REVOKE permission deletes a previous GRANT or DENY. The DENY permission prohibits access even when access permissions have been granted because explicit DENY trumps all other permissions.

In this section, each of these methods will be applied in a Visual Basic example and in a Transact-SQL example. The following Transact-SQL code grants Bob and Jane permissions to SELECT from the authors table, and grants Jane permissions to INSERT into the titles table:

/* Grant permissions to SELECT. */
GRANT SELECT
ON authors
TO Bob, [REDMOND\Jane]
GO
/* Grant permissions to INSERT. */
GRANT INSERT
ON titles
TO [REDMOND\Jane]
GO

The previous example shows how the GRANT statement works when permissions are granted to explicit users of the database (Bob) and when permissions are granted to a Windows user (Jane).

Here is the same example in Visual Basic:

' Declare variables.
Dim oServer As SQLDMO.SQLServer
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Grant Jane and Bob permissions to select from the authors table.
oServer.Databases("pubs").Tables("authors").Grant SQLDMOPriv_Select, "Bob"
oServer.Databases("pubs").Tables("authors").Grant SQLDMOPriv_Select, _
"[REDMOND\Jane]
' Grant Jane permissions to select from the authors table.
oServer.Databases("pubs").Tables("authors").Grant SQLDMOPriv_Select, _
"[REDMOND\Jane]"

In the previous examples, there is little difference between granting access to a user by fully qualifying their domain name, and directly granting access to a user who already has permissions to access the database. Due to these similarities, the following examples will show only the code for existing database users.

The following Transact-SQL statement shows how a user can be denied SELECT permissions:

/* Deny permissions to SELECT. */
DENY SELECT
ON authors
TO Bob
GO
And again using Visual Basic:
' Declare variables.
Dim oServer As SQLDMO.SQLServer
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Deny Bob permissions to select from authors table.
oServer.Databases("pubs").Tables("authors").Deny SQLDMOPriv_Select, "Bob"

Here is a Transact-SQL example that shows how to revoke permissions from a user:

/* Revoke permissions to SELECT. */
REVOKE SELECT
ON authors
FROM Bob
GO
Here is the Visual Basic code:
' Declare variables.
Dim oServer As SQLDMO.SQLServer
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Revoke Bob permissions to select from the authors table.
oServer.Databases("pubs").Tables("authors").Revoke SQLDMOPriv_Select, "Bob"

Ownership Chains

When multiple database objects access each other sequentially, the sequence is known as a "chain." Although such chains have no independent existence, when SQL Server is traversing the links in a chain it evaluates user permissions on the constituent objects differently than it would if it were accessing them separately. These differences have important implications for managing security.

How permissions are checked in a chain

When an object is accessed through a chain, SQL Server first compares its owner to the owner of the calling object (the previous link in the chain). If both objects have the same owner, permissions on the referenced object are not evaluated.

Example of ownership chaining

In Figure 3 below, the July2003 view is owned by Mary. She has granted to Alex permissions on the view. He has no other permissions on database objects in this instance. What happens when Alex selects the view?

Alex selects * on the July2003 view. SQL Server checks permissions on the view and confirms that Alex has permission to select on it.
The July 2003 view needs information from the SalesXZ view. SQL Server checks the ownership of SalesXZ view. Since it has the same owner (Mary) as the view that calls it, its permissions are not checked. Required information is returned.
SalesXZ view needs information from InvoicesXZ view. SQL Server checks the ownership of InvoicesXZ view. Since it has the same owner as the previous object, its permissions are not checked. Required information is returned. To this point, all items in the sequence have had a single owner (Mary). This is known as an "unbroken ownership chain."
InvoicesXZ view needs information from AcctAgeXZ view. SQL Server checks the ownership of AcctAgeXZ view. Since it has a different owner than the previous item (Sam, not Mary), full information about permissions on this view is retrieved. If AcctAgeXZ view has permissions that allow access by Alex, information will be returned.
AcctAgeXZ view needs information from ExpenseXZ table. SQL Server checks the ownership of ExpenseXZ table. Since it has a different owner than the previous item (Joe, not Sam), full information about permissions on this table is retrieved. If ExpenseXZ table has permissions that allow access by Alex, information is returned.
When July2003 view attempts to retrieve information from ProjectionsXZ table, the server first checks to see whether cross-database chaining is enabled between Database 1 and Database 2. If it is enabled, the server will check the ownership of ProjectionsXZ table. Since this table has the same owner as the calling view (Mary), its permissions are not checked. Requested information is returned.

Figure 3:

Value of ownership chaining

Ownership chaining makes it possible to manage access to multiple objects (for example, multiple tables) by setting permissions on a single object (for example, a view). It also offers a slight performance advantage in scenarios that permit the skipping of permission checks.

Cross-database ownership chaining

SQL Server can be configured to permit ownership chaining between specific databases or across all databases inside a single instance. Cross-database ownership chaining is disabled by default, and should not be enabled unless it is specifically required.

Potential threats

Ownership chaining is very useful in managing permissions on a database, but it does assume that object owners anticipate the full consequences of every decision to grant permission on an object. In Figure 3 Mary owns most of the July2003 view's underlying objects. Since she has the right to make objects she owns accessible to any other user, SQL Server behaves as though whenever Mary grants access to the first view in a chain she has made a conscious decision to share the views and table it references. In real life, this might not be a valid assumption. Production databases are far more complex than the one in Figure 3 and the permissions that regulate access to them rarely map perfectly to the administrative structures of the organizations that use them.

It is important to understand that members of highly privileged database roles can use cross-database ownership chaining to access objects in databases external to their own. For example, if cross-database ownership chaining is enabled between database A and database B, a member of the db_owner fixed database role of either database can spoof her way into the other database. The process is simple: Diane (a member of db_owner in database A) creates user Stuart in database A. Stuart already exists as a user in database B. Diane then creates an object (owned by Stuart) in database A that calls any object owned by Stuart in database B. Since the calling and called objects have a common owner, permissions on the object in database B will not be checked when Diane accesses it through the object she has created.

Note: Ownership chaining applies to Data Manipulation Language (DML) operations (SELECT, UPDATE, INSERT, DELETE) but not to Data Definition Language (DDL) operations.

SQL Server 2000 Security Model

On This Page

Introduction

Assumptions

SQL Server 2000 Security Model

Authentication Modes

Windows Authentication Mode

Mixed Mode

Using Security Identification Numbers Internally

Roles

Fixed Server Roles

Fixed Database Roles

Securing Access to the Server

Windows Level

Securing Access to the Database

SQL Server Level

Securing Access to the Database Objects

Additional resources