You control database access and administration with database users and roles. Database users are the logins that have the right to access the database. Database access roles set administration privileges and other database permissions.
For individual logins, you can grant access to databases and assign roles by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server, and then work your way down to the Security folder.
Expand the Security folder and the Logins folder to list the current logins. Right-click the login you want to configure, and then select Properties. This opens the SQL Server Login Properties dialog box.
Select the User Mapping page, as shown in Figure 8-5.
Select the check box for a database that you want the login to be able to access. Then, in the Database Role Membership For... list box, select the check boxes next to the database roles that this login should have on the currently selected database.
Repeat step 5 for other databases that you want the login to be able to access.
When you have finished configuring database roles, click OK.
At the database level, you can assign database roles to multiple logins. To do this, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server.
Expand the Databases folder, and then expand the node for the database you want to configure.
Expand the database’s Security, Roles, and Database Roles folders. Double-click the role you want to configure. This opens the Database Role Properties dialog box shown in Figure 8-6.
To add role members, click Add to display the Select Database User Or Role dialog box.
In the dialog box, enter the name of the user or role to add. Separate names with semicolons. You can enter partial names, and then click Check Names to expand the names. To search for names, click Browse.
To remove a role member, select a database user or other role, and then choose Remove.
When you have finished configuring database roles, click OK.
Although predefined roles have a specific set of permissions that you cannot change, you can set permissions for roles you create for a particular database. For example, suppose that a database has three different types of users: normal users who need to view data, managers who need to be able to modify data, and developers who need to be able to modify database objects. In this situation, you can create three roles to handle these user types. Then you need to manage only these roles and not the many different user accounts.
To create a standard database role, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server.
Expand the Databases folder, and then select a database and expand the node for it.
Expand the database’s Security and Roles folders. Right-click Roles, point to New, and then choose New Database Role. This opens the Database Role dialog box shown in Figure 8-7.
Type a name for the role in the Role Name box.
The default owner of the role is dbo. To set a different owner, click the button to the right of the Owner box to display the Select Database User Or Role dialog box.
In the dialog box, enter the name of the users, the roles, or both that should be the owners of this role. Separate names with semicolons. You can enter partial names, and then click Check Names to expand the names. To search for names, click Browse.
To add role members, click Add to display the Select Database User Or Role dialog box.
In the dialog box, enter the names of the users or roles to add. Separate names with semicolons. You can enter partial names, and then click Check Names to expand the names. To search for names, click Browse. Click OK.
Choose Permissions from the Select A Page list in the dialog box, and then use the Permissions page options to configure database access permissions for this role. For more information about configuring database access permission, see the section titled "Managing Database Permissions" later in this chapter.
Click OK.
Application roles are designed to be used by applications that access the database and do not have logins associated with them. You can configure an application role by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server.
Expand the Databases folder, and then select a database and expand the node for it.
Expand the database’s Security and Roles folders. Right-click Roles, point to New, and then choose New Application Role. This opens the Application Role – New dialog box shown in Figure 8-8.
Type a name for the role in the Role Name box.
The default schema for the role is dbo. The default (or base) schema sets the base permissions for the new role. To set a different default schema, click the button to the right of the Default Schema box to display the Locate Schema dialog box.
In the dialog box, enter the name of the default schema. You can enter a partial name, and then click Check Names to expand the name. To search for a schema to use, click Browse. Click OK.
Choose Permissions from the Select A Page list, and then use the Permissions page options to configure database access permissions for this role. For more information about configuring database access permission, see the section titled "Managing Database Permissions" later in this chapter.
Click OK.
To revoke access rights or to remove a user from a role in a database, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server.
Expand the Databases folder, and then select a database and expand the node for it.
Expand the database’s Security and Users folders. Double-click the user name. This opens the Database User dialog box.
On the General page, clear the check box next to the database roles that this user should not have on the currently selected database.
When you have finished removing roles for the database user, click OK.
To delete a user-defined role, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server.
Expand the Databases folder, and then select a database and expand the node for it.
Expand the database’s Security and Roles folders.
If the role you want to remove is a Database Role, expand Database Roles. If the role you want to remove is an Application Role, expand Application Roles.
Select the role you want to delete, and then press the DELETE key.
The Delete Object dialog box shows you which role you are deleting. Click OK to remove the role.
SQL Server provides different commands for managing database access and roles. These commands are summarized in Example 8-11.
Example 8-11. Commands for Managing Database Access and Roles
Adding a User to Current Database
CREATE USER user_name
[ FOR
{ LOGIN login_name
| CERTIFICATE cert_name
| ASYMMETRIC KEY asym_key_name
}
]
[ WITH DEFAULT_SCHEMA = schema_name ]Renaming a User or Changing Default Schema
ALTER USER user_name
WITH < set_item > [ ,...n ]
< set_item > ::=
NAME = new_user_name
| DEFAULT_SCHEMA = schema_nameRemoving a User from a Database
DROP USER user_name
Displaying Permissions of Database Roles
sp_dbfixedrolepermission [[@rolename =] 'role']
Managing Database Standard Roles
CREATE ROLE role_name [ AUTHORIZATION owner_name ] ALTER ROLE role_name WITH NAME = new_name DROP ROLE role_name sp_helprole [[@rolename =] 'role']
Managing Database Role Members
sp_addrolemember [@rolename =] 'role', [@membername =] 'security_account' sp_droprolemember [@rolename =] 'role', [@membername =] 'security_account' sp_helprolemember [[@rolename =] 'role']
Managing Application Roles
sp_addapprole [@rolename =] 'role', [@password =] 'password'
sp_dropapprole [@rolename =] 'role'
sp_setapprole [@rolename =] 'role' ,
[@password =] {Encrypt N 'password'} | 'password'
[,[@encrypt =] 'encrypt_style']