SQL Server Security: Roles

Roles provide a consistent yet flexible model for security administration. Roles are similar to the groups used in administering networks. Permissions are applied to the role, and then members are added to the role; therefore, any member of the role has all the permissions that the role has. A user can be a member of multiple roles and, with the exception of application roles, all permissions of all roles will apply. This makes possible a modular model for permission administration. For example, all permissions required for sales personnel can be assigned to the "sales" role. All permissions required for the managers could be assigned to the "managers" role. To implement security in the sales department, assign the sales users to the "sales" role and the sales managers to both the "sales"and "managers" roles. Roles are also useful to group permissions required for a particular application, as opposed to function or department. If a user is required to use the application, he can simply be added to its associated role.

SQL Server uses roles in three ways:

• Fixed-server and fixed-database roles, which have predefined permissions

• User-defined roles, which are created in each database to manage users

• Application roles, which have special properties useful in applying security to custom applications

Fixed-Server Roles

As the name implies, fixed-server roles are used to manage permissions at the server level and cannot be modified. Fixed-server roles are used to delegate various levels of administrative privileges by adding logins as members of the roles. This concept of delegation is important, as you can maintain security while distributing the administrative workload. A typical use of this would be to assign the network account administrators to the securityadmin role, allowing them to create logins on SQL Server. The fixed-server roles are listed in Table 15.1.

Table 15.1. Fixed-Server Roles

Role	Permission
sysadmin	Perform any activity
dbcreator	Create, alter, and drop databases
diskadmin	Create and manage disk files
processadmin	Manage SQL Server processes
serveradmin	Change server configuration and shut down the server
setupadmin	Set up replication and manage linked servers
securityadmin	Manage SQL Server logins and passwords, read error logs, and manage CREATE DATABASE permissions
bulkadmin	Execute the BULK INSERT statement

Managing Fixed-Server Roles

To change membership in a fixed-server role, you can use the system-stored procedures sp_addsrvrolemember and sp_dropsrvrolemember. For example, to add the Windows user domain1\bobw to the sysadmin role and drop the SQL login bwhite, the syntax would be as follows :

 EXEC sp_addsrvrolemember 'domain1\bobw', 'sysadmin'  Go EXEC sp_dropsrvrolemember 'bwhite', 'sysadmin' 

If you prefer to use Enterprise Manager, in the Security folder, use either the Server Roles tab of a login's Properties dialog box or select a server role and from its Properties dialog box, click Add and select the appropriate login(s). Figure 15.2 shows the Properties page for a fixed-server role.

Figure 15.2. The Server Role Properties page.

Fixed-Database Roles

Each database also has a predefined set of roles used to delegate permissions at the database level. As in the fixed-server roles, the permissions that apply to these roles are preset and can't be altered . The exception to this rule is the public role. The public role cannot be deleted, but permissions to access database objects can be applied to it. As all users are members of the public role, any permissions applied to the role will affect all users. Table 15.2 shows the fixed-database roles and their permissions.

Table 15.2. Fixed-Database Roles

Role	Permission
public	All default permissions
db_owner	Perform any database activity
db_ddladmin	Add, drop, or modify database objects
db_accessadmin	Add or remove database users or roles
db_securityadmin	Assign statement and object permissions, and manage role membership
db_backupoperator	Back up the database, and run DBCC statements and CHECKPOINT
db_datareader	Read (select) from any user table
db_datawriter	Insert, update, or delete on any user table
db_denydatareader	Cannot read (select) from any user table
db_denydatawriter	Cannot insert, update, or delete on any user table

Managing Fixed-Database Roles

To change membership in a fixed-database role with T-SQL, use the system-stored procedures sp_addrolemember and sp_droprolemember. The following statement adds the database user bob to the db_owner role and drops the user tom.

 EXEC sp_addrolemember 'db_owner' , 'bob'  GO EXEC sp_droprolemember 'db_owner', 'tom' 

If you prefer to use Enterprise Manager, select the database you want to administer, select a user from the Users folder, and from the General tab of the User Properties dialog box, check the appropriate roles. Alternatively, select the database role from the Roles folder, and from its Properties dialog box, click Add and select the appropriate login(s). Figure 15.3 shows the Properties page for a fixed database role.

Figure 15.3. The Database Role Properties page.

User-Defined Roles

User-defined roles, or database roles, are used to provide a common set of permissions to a group of users. Whereas fixed roles are used to provide statement-level permissions (with the exception of public), user-defined roles are used to provide object permissions in the database. Roles can contain users and other roles, which can be useful for designing a modular security model. An example of this would be to create a role for an application ”such as HR_App ”and assign the permissions to it that are required to run the HR application. You could then create roles called HR_Users and HQ_Users and add the appropriate users to these roles. Now add the HR_Users and HQ_Users to the HR_App role to provide all users in those roles the needed permissions to run the application. Note that if you are using NT groups to manage users, they too could be added to the application role to facilitate permission management.

For a full discussion on creating and managing user-defined roles, see the later section, "Managing Database Roles."

Application Roles

Unlike other roles, application roles contain no database users. When an application role is created (see the later section, "Managing Database Roles"), rather than adding a list of users who belong to the role, a password is specified. To obtain the permissions associated with the role, the connection must "set" the role and supply the password. This is done using the stored procedure sp_setapprole . The syntax to set the role to the sales application role (with a password of "qwerty") is as follows:

 EXEC sp_setapprole 'sales', 'qwerty' 

You can also encrypt the password:

 EXEC sp_setapprole 'sales', {ENCRYPT N 'qwerty'}, 'odbc' 

When an application role is "set," all permissions from that role apply and all permissions inherited from roles other than public are suspended until the session is ended. So why is it called an application role? The answer is in how it is used. An application role is used to provide permissions on objects through an application, and only through the application. Remember that you must use sp_setapprole and provide a password to activate the role; this statement and password are not given to the users; rather, they are imbedded in the application connect string. This means that the only way the user gets the permissions associated with the role is if they are running the application. The application can have checks and balances written into it to ensure that these permissions are being used for the forces of good and not evil.