Managing Access to SQL Server Databases

Granting access to a SQL Server instance is not enough for applications that need access to data. After granting access to the SQL Server instance, you need to grant access to specific databases. You grant access to databases by creating database users and mapping logins to database users. Each login is mapped to a database user for each database to which the login needs access. Each database user is mapped to one login only with the exception of the dbo database user.

Note

In earlier versions of SQL Server, you could use the system stored procedure sp_addalias to map multiple logins to a single database user. You can still do so in SQL Server 2005. However, you should not use this feature because it is considered outmoded, and it might be removed from future versions.

Granting Access to Databases

Database users are the principals at the database level. All logins, except members of the sysadmin server role, need to be mapped to a user mapped for databases to which they need access. Members of the sysadmin role are mapped to the dbo user on all server databases.

Creating Database Users

You can create database users by using the CREATE USER statement. The following Transact SQL sample creates a login called Peter and a mapped user in the AdventureWorks database:

-- Create the login Peter CREATE LOGIN Peter WITH PASSWORD='Tyu87IOR0'; -- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Create the database user Peter, -- mapped to the login Peter in the database AdventureWorks. CREATE USER Peter FOR LOGIN Peter;

Managing Database Users

You can check whether the current login has access to a database by executing the following statement:

SELECT HAS_DBACCESS('AdventureWorks');

To obtain information from database users, you can use the sys.database_principals catalog view.

If you need to disable database access temporally for a database user, you can revoke the CONNECT permission to that user. The following sample revoke, the CONNECT permission to Peter:

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Revoke connect permission from Peter -- on the database AdventureWorks. REVOKE CONNECT TO Peter;

You can drop a database user using the DROP USER statement.

Caution

SQL Server 2005 does not allow you to drop a user that owns a database schema. You will learn about schemas later in this chapter.

Managing Orphaned Users

Orphaned users are database users that are not mapped to a login in the current SQL Server instance. In SQL Server 2005, a user can become orphaned when you drop the mapped login. In order to obtain information about orphaned users, you can execute the following code:

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Report all orphaned database users EXECUTE sp_change_users_login @Action='Report';

SQL Server 2005 allows you to create a user not mapped to a login by using the WITHOUT LOGIN clause. Users created with the WITHOUT LOGIN clause are not considered to be orphaned users. This feature can be very useful in scenarios where you need to change the execution context of a module. You will learn about execution contexts later in this chapter. The following sample creates a user not mapped to a login:

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Creates the database user Paul in the AdventureWorks database -- without mapping it to any login in this SQL Server instance CREATE USER Paul WITHOUT LOGIN;

Enabling Guest Users

When a login that doesn't have a mapped user tries to connect to a database, SQL Server tries to connect using the Guest user. The Guest user is created by default without permissions. You can enable the guest user by granting the CONNECT permission to the Guest user, as shown below.

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Grant Guest access to the AdventureWorks database. GRANT CONNECT TO Guest;

Use caution when considering whether to enable guest users because they add potential security risks to the database environment.

Granting Database Permissions

Once you have created database users, you need to manage permissions for them. You can manage a user's permissions by adding the user to database roles or by granting granular permissions to the user.

Creating Database Roles

Database roles are database-level principals. You can use database roles to assign database permissions to a group of users. SQL Server 2005 creates a set of default database roles. The default roles are listed in Table 2-3.

Table 2-3. Default Database Roles

Database Role	Description
db_accessadmin	Can manage access to databases
db_backupoperator	Can back up databases
db_datareader	Can read all data from all user tables
db_datawriter	Can add, delete, and update data in all user tables
db_ddladmin	Can execute any DDL command in a database
db_denydatareader	Cannot read any data in user tables
db_denydatawriter	Cannot add, update, or delete any data in user tables
db_owner	Can perform all configuration and maintenance activities
db_securityadmin	Can modify database role membership and manage permissions
public	A special database role. All database users belong to the public role. You cannot remove users from the public database role.

You can add database roles in order to group users according to specific permission requirements. The following sample creates a database role called Auditors and adds the user Peter to the new role.

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Create the role Auditors in the database AdventureWorks. CREATE ROLE Auditors; GO -- Add the user Peter to the role Auditors EXECUTE sp_addrolemember 'Auditors', 'Peter';

Managing Database Roles

You can check if the current user belongs to a database role by querying the IS_MEMBER system function. In the following sample, you check if the actual user belongs to the db_owner database role.

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Checking if the current user belogs to the db_owner role SELECT IS_MEMBER ('db_owner');

Tip

You can use the IS_MEMBER system function to check whether the current user belongs to a specific Windows group as well, as in the following example:

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Checking if the current user belogs to the Managers group -- in the ADVWORKS domain SELECT IS_MEMBER ('[ADVWORKS\Managers]');

Users can be removed from a database role by using the sp_droprolemember system stored procedure. If you need to drop a database role, you can use the DROP ROLE statement. The following code removes Peter from the Auditors database role and then removes the Auditors role.

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Drop the user Peter from the Auditors role EXECUTE sp_droprolemember 'Auditors', 'Peter'; -- Drop the Auditors role from the current database DROP ROLE Auditors;

Warning

SQL Server 2005 does not allow you to remove a role that contains members. You must remove all users from the database role before dropping the role.

Granting Granular Database Permissions

As an alternative to using fixed database roles, you can grant granular permissions to database roles and users. You can manage permissions by using the GRANT, DENY, and REVOKE statements. In the following sample, you grant the BACKUP DATABASE permission to Peter user:

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Grant permissions to the database user Peter -- to backup the database AdventureWorks. GRANT BACKUP DATABASE TO Peter;

When you use the DENY statement to remove a permission from a user, the user cannot inherit the same permission by being a member of a database role that has this permission. However, if you use the REVOKE statement to remove the permission, the user can inherit the same permission by being a member of a database role with this permission already granted. Use the REVOKE statement only to remove a permission that had previously been granted.

Best Practices

To reduce the effort required to maintain a permission structure, you should assign permissions to database roles only instead of assigning permissions to individual database users.

Managing Application Roles

Application roles are special database roles that you can use to allow access to specific data only to users that connect through a specific application. Application roles do not contain members, and you should activate them in the current connection before use. When you activate an application role, the current connection loses its user-specific permissions and gets only the permissions applicable to the application role.

Creating Application Roles

You can create an application role by using the CREATE APPLICATION ROLE statement. This code is included in the sample file ApplicationRoles.sql.

-- Change the connection context to the database AdventureWorks. USE AdventureWorks GO -- Create the Application role FinancialRole -- in the current database CREATE APPLICATION ROLE FinancialRole WITH PASSWORD = 'Pt86Yu$$R3';

Using Application Roles

Application roles must be activated before you can use them. You can activate application roles by executing the sp_setapprole system stored procedure. Once you activate an application role in the current connection, it remains activated until the connection is closed or you execute the sp_unsetapprole system stored procedure. Although application roles are meant to be used from client applications, you can use them from ad-hoc T-SQL batches as well. The following procedure (included in the sample files as ApplicationRolesUse.sql) describes how to activate the FinancialRole application role and how to revert this activation.

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Declare a variable to hold the connection context. -- We will use the connection context later -- so that when the application role is deactivated -- the connection recovers its original context. DECLARE @context varbinary (8000); -- Activate the application role -- and store the current connection context EXECUTE sp_setapprole 'FinancialRole', 'Pt86Yu$$R3', @fCreateCookie = true, @cookie = @context OUTPUT; -- Verify that the user's context has been replaced -- by the application role context. SELECT CURRENT_USER; -- Deactivate the application role, -- recovering the previous connection context. EXECUTE sp_unsetapprole @context; GO -- Verify that the user's original connection context -- has been recovered. SELECT CURRENT_USER; GO

Dropping Application Roles

If you need to drop an application role, use the DROP APPLICATION ROLE statement, as shown below (this code is included in the sample file ApplicationRoles.sql):

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Drop the application role FinancialRole -- from the current database DROP APPLICATION ROLE FinancialRole;