SQL Server 2008 : Roles

Like Windows groups, SQL Server provides two roles, server- and database-level roles into which logins and users can be added. Server-level roles are fixed roles that have a serverwide permission scope. Each built-in role serves a specific purpose and have the required permissions associated with them. Although you are limited to the built-in server-level roles, you can create new database-level roles in addition to those available to suit more specific needs.

SQL Server logins, Windows accounts, and Windows groups can be added to server-level roles. Server-level roles are as follows:

• sysadmin – Perform any activity in the server. By default, the BUILTIN\Administrators group and the local administrator’s group are members of the sysadmin role.

• serveradmin – Change server-wide configuration options and shut down the server.

• securityadmin – Manage logins and their properties. They will be able to reset passwords for SQL Server logins and GRANT, DENY, and Revoke database-level and server-level permissions.

• processadmin – End processes running in an instance of SQL Server.

• setupadmin – Add and remove linked servers.

• bulkadmin – Run the BULK INSERT statement.

• diskadmin – Manage disk files.

• dbcreator – CREATE, ALTER, DROP, and restore any database.

You can find the following objects in the master database that can help when working with server-level roles:

• sp_helpsrvrole – Returns a list of server-level roles.

• sp_helpsrvrolemember – Returns information about the members of a server-level role.

• sp_srvrolepermission – Displays the permissions of a server-level role.

• IS_SRVROLEMEMBER – Indicates whether a SQL Server login is a member of the specified server-level role.

• sys.server_role_members – Returns one row for each member of each server-level role.

• sp_addsrvrole_member – Adds a login as a member of a server-level role.

• sp_dropsrvrole_member – Removes a SQL Server login or a Windows user or group from a server-level role.

There are two types of database-level roles, fixed database roles that are predefined in the database and flexible database roles that you can create.

The fixed database-level roles are:

• db_owner – Can drop the database as well as permission to perform all configuration and maintenance tasks.

• db_security_admin – Can modify role membership and manage permissions. Please be careful when adding principals to this role; an unintended privilege escalation could result.

• db_accessadmin – Can add or remove database access for Windows logins, Windows groups, and SQL Server logins.

• db_backupoperator – Can back up the database.

• db_ddladmin – Can run any Data Definition Language command.

• db_datawriter – Can add, delete, or change data in all user tables.

• db_datareader – Can read all data from all user tables.

• db_denydatawriter – Will deny permission in the database to add, modify, or delete any data in the user tables.

• db_denydatareader – Will deny permission in the database to read any data in the user tables.

These objects can be helpful when working with Database-level roles:

• sp_helpdbfixedrole – Returns a list of the fixed database roles.

• sp_dbfixedrolepermission – Displays the permissions of a fixed database role.

• sp_helprole – Returns information about the roles in the current database.

• sp_helprolemember – Returns information about the members of a role in the current database.

• sys.database_role_members – Returns one row for each member of each database role.

• IS_MEMBER – Indicates whether the current user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role.

• CREATE_ROLE – Creates a new database role in the current database.

• ALTER_ROLE – Changes the name of a database role.

• DROP_ROLE – Removes a role from the database.

• sp_addrole – Creates a new database role in the current database.

• sp_droprole – Removes a database role from the current database.

• sp_addrolemember – Adds a database user, database role, Windows login, or Windows group to a database role in the current database.

• sp_droprolemember – Removes a security account from a SQL Server role in the current database.

Exam Warning

Understand the difference between Server-level and Database-level Roles as well as their permissions scope.

Figure 1 shows the fixed server roles. Figure 2 shows the predefined database roles.

Figure 1. Fixed Server-Level Roles

Figure 2. Database-Level Roles

1. Create a New Database Role

You need to create a new Database Role owned by the HumanResources role that will permit members to also use objects in the purchasing schema.

1. In the SQL Server Management Studio, locate the Database Roles folder under Security/Roles in the AdventureWorks2008 database.

2. Right-click on the Database Roles folder and select New Database Role... from the menu.

3. In the Database Role – New window, type Purchasing into the Role name text box.

4. In the Owner text box, browse and select HumanResources as the Owner.

5. In the Owned Schemas section, locate and select the Purchasing schema.

6. Your window should look like Figure 3. By creating your new role, notice the schemas owned by your new role by default.

   Figure 3. Database Role Properties

   
   

   

7. Click OK. Your newly created database role will appear in the database role folder.