Administering Database Roles

3 4

You can simplify the task of managing many permissions to many users by using database roles. Database roles are designed to allow groups of users to receive the same database permissions without your having to assign these permissions individually. Rather than assigning individual permissions to individual users, you can create a role that represents the permissions used by a group of users and then assign it to the group.

Typically, roles are set up for particular workgroups, job classes, or job tasks. In this manner, new users can be members of one or more database roles based on the jobs they will be performing. For example, roles might be defined for job classes such as accounts payable, accounts receivable, engineering, and human resources. When a user joins one of these departments or groups, he or she is simply assigned as a member of the role created for that group. A user can be a member of one or more roles, but the user is not required to be a member of any roles. In addition to being assigned as a member of a database role, a user can be assigned individual permissions.

Creating and Modifying Roles

You accomplish the tasks of creating and modifying database roles by using the same tools that you use to perform most tasks related to database administration: Enterprise Manager or TSQL commands. (SQL Server does not provide a wizard for these tasks.) Whichever method you use, you must accomplish the following tasks when you implement a role:

• Create the database role.
• Assign permissions to the role.
• Assign users to the role.

When viewing a role, you will be able to see both the permissions assigned to the role and the users assigned to the role.

Using Enterprise Manager to Administer Roles

To create database roles by using Enterprise Manager, follow these steps:

1. Expand a server group, expand a server, and then expand the Databases folder. Right-click the database you want to create the role in (we will use Northwind for this example), point to New in the shortcut menu, and then choose Database Role. Alternatively, you can expand the database, right-click Roles, and choose New Database Role from the shortcut menu. Either way, the Database Role Properties window appears, as shown in Figure 34-18.

   

   Figure 34-18. The Database Role Properties window.

2. Assign a descriptive name to the role by typing the name in the Name text box—choose a name that will help you remember the function of the role. Figure 34-18 shows the name Accounts Payable chosen for a role.
3. To assign users to the role, click Add. A list of user accounts that have access to the database appears, as shown in Figure 34-19. Select the users you want to assign to this role. To cancel a selection, simply click the appropriate user name again. When you have finished modifying the membership of the role, click OK and the role will be created. You will be returned to the Enterprise Manager window.

   

   Figure 34-19. The Add Role Members dialog box.

4. To assign permissions to the role, first open the Database Role Properties window by expanding the Roles folder, right-clicking the role name, and choosing Properties from the shortcut menu. Then click Permissions to display the Database Role Properties - Northwind window, as shown in Figure 34-20.

   

   Figure 34-20. The Database Role Properties - Northwind window.

   In this window, you can assign various permissions to this role for the objects within the database containing the role. To do so, select the appropriate check boxes in the list box. The database objects are listed in the Object column. You can use the option buttons at the top of the window to view all objects or just those for which this role already has permissions. Once you assign this role to a user, the user will receive all of the permissions that have been assigned to the role.

Once you create a role, you can modify it in the Database Role Properties window. To modify a role, follow the steps used to add permissions to a role. You can add and delete users and permissions in the Database Role Properties window.

Using T-SQL to Administer Roles

You can also create roles by using the sp_addrole stored procedure. The sp_addrole stored procedure has the following syntax:

 sp_addrole [ @rolename = ] 'role' [ , [ @ownername = ] 'owner' ] 

For example, to add a role named readonly to the Northwind database, use the following T-SQL command:

 USE Northwind GO sp_addrole 'readonly' , 'dbo' GO 

The USE Northwind command will select Northwind as your current database. If you do not specify a database, the role will be created in your default database.

This stored procedure will only create the role. To add permissions to the role, use the GRANT statement, which was described earlier. To remove permissions from the role, use the REVOKE statement, also described earlier.

For example, to add the SELECT permission on the Employees, Customers, and Orders tables to the readonly role, use the following GRANT statement:

 USE Northwind GO GRANT SELECT ON Employees TO readonly GO GRANT SELECT ON Customers TO readonly GO 

To add users to the role, use the sp_addrolemember stored procedure. The sp_addrolemember stored procedure has the following syntax:

 sp_addrolemember 'role', 'security_account' 

The following command adds a user to the readonly role:

 USE Northwind GO sp_addrolemember 'readonly' , 'Guest' GO 

Using Fixed Server Roles

A number of predefined roles that apply at the server level are created when SQL Server is installed. These fixed server roles are used to grant permissions to DBAs and can contain both server permissions and object and statement permissions. These roles are listed here:

• bulkadmin Can perform bulk inserts
• dbcreator Can create and alter databases
• diskadmin Can manage disk files
• processadmin Can manage SQL Server processes
• securityadmin Can manage logins and create database permissions
• serveradmin Can set any server options and can shut down the database
• setupadmin Can manage linked servers and startup procedures
• sysadmin Can perform any server activity

By assigning user accounts to fixed server roles, you enable users to perform the administrative tasks that those roles have permissions for. Depending on your needs, this setup might be preferable to having all DBAs use the same administrative account. Like database roles, fixed server roles are much easier to maintain than individual permissions, but fixed server roles cannot be modified. You can assign a user to a fixed server role by following the steps listed next.

1. In Enterprise Manager, expand a server group, expand a server, expand the Security folder, and then click Server Roles. Right-click the fixed server role you want to add the user to and choose Properties from the shortcut menu. This invokes the Server Role Properties window, shown in Figure 34-21.

   

   Figure 34-21. The Server Role Properties window.

2. To add a user account to the role, first click Add. This invokes the Add Members dialog box, shown in Figure 34-22, in which you select new role members.

   

   Figure 34-22. The Add Members dialog box.

3. After you have selected the users that you want to add to the fixed server role, click OK. This will return you to the Server Role Properties window. Click OK to add the user to the security role.