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.
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:
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.
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.
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');
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;
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.
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;