Roles are a lot like Windows—they allow you to assign permissions to a group of users easily and they can have built-in permissions (implicit permissions) that cannot be changed. Two types of roles are available:
Server roles. Applied at the server level
Database roles. Applied at the database level
You use server roles to grant server administration capabilities. If you make a login a member of a role, users who use this login can perform any tasks permitted by the role. For example, members of the sysadmin role have the highest level of permissions on SQL Server and can perform any type of task.
You set server roles at the server level, and you predefine them. This means that these permissions affect the entire server and you cannot change the permission set. The following list provides a summary of each server role, from the lowest-level role (bulkadmin) to the highest-level role (sysadmin):
bulkadmin. Designed for domain accounts that need to perform bulk inserts into the database. Members of this role can add members to bulkadmin and can execute the BULK INSERT statements.
dbcreator. Designed for users who need to create, modify, drop, and restore databases. Members of this role can add members to dbcreator and perform these tasks: ALTER DATABASE, CREATE DATABASE, DROP DATABASE, EXTEND DATABASE, RESTORE DATABASE, RESTORE LOG, and sp_renamedb.
diskadmin. Designed for users who need to manage disk files. Members of this role can add members to diskadmin and can perform these tasks: DISK INIT, sp_addumpdevice, sp_diskdefault, and sp_dropdevice.
processadmin. Designed for users who need to control SQL Server processes. Members of this role can add members to processadmin and can kill processes.
securityadmin. Designed for users who need to manage logins, create database permissions, and read error logs. Members of this role can add members to securityadmin; grant, deny, and revoke server-level and database-level permissions; reset passwords; and read the error logs. In addition, they can also perform these tasks: sp_addlinkedsrvlogin, sp_addlogin, sp_defaultdb, sp_defaultlanguage, sp_denylogin, sp_droplinkedsrvlogin, sp_droplogin, sp_grantlogin, sp_helplogins, sp_remoteoption, and sp_revokelogin.
serveradmin. Designed for users who need to set server-wide configuration options and shut down the server. Members of this role can add members to serveradmin and can perform these other tasks: DBCC FREEPROCCACHE, RECONFIGURE, SHUTDOWN, sp_configure, sp_fulltext_service, and sp_tableoption.
setupadmin. Designed for users who need to manage linked servers and control startup procedures. Members of this role can add members to setupadmin; can add, drop, and configure linked servers; and can control startup procedures.
sysadmin. Designed for users who need complete control over SQL Server and installed databases. Members of this role can perform any activity in SQL Server.
Fixed server roles can be mapped to the more granular permissions for SQL Server 2005, as shown in Table 8-6.
Table 8-6. Granular Permissions Associated with Fixed Server Roles
Fixed Server Role | Permissions Granted with This Role |
|---|---|
bulkadmin | ADMINISTER BULK OPERATIONS |
dbcreator | CREATE DATABASE |
diskadmin | ALTER RESOURCES |
processadmin | ALTER SERVER STATE, ALTER ANY CONNECTION |
securityadmin | ALTER ANY LOGIN |
serveradmin | ALTER SETTINGS, SHUTDOWN, CREATE ENDPOINT, ALTER SERVER STATE, ALTER ANY ENDPOINT, ALTER RESOURCES |
setupadmin | ALTER ANY LINKED SERVER |
sysadmin | CONTROL SERVER |
When you want to assign permissions at the database level, you can use database roles. You set database roles on a per database basis, which means that each database has its own set of roles. SQL Server 2005 supports three types of database roles:
User-defined standard roles
User-defined application roles
Predefined (or fixed) database roles
Standard roles allow you to create roles with unique permissions and privileges. You can use standard roles to logically group users together and then assign a single permission to the role rather than having to assign permissions to each user separately. For example, you could create a role called Users that allows users to SELECT, INSERT, and UPDATE specific tables in the database but does not allow them to perform any other tasks.
Application roles allow you to create password-protected roles for specific applications. For example, a user could connect through a Web-based application called NetReady; this application would activate the role and the user would then gain the role’s permissions and privileges. Standard database roles or other roles cannot be assigned to an application role. Instead, the application role is activated when the application connects to the database.
SQL Server also has predefined database roles. Predefined roles are built in, and they have permissions that cannot be changed. You use predefined roles to assign database administration privileges, and you can assign a single login to multiple roles. These privileges are summarized in the following list:
public. The default role for all database users. Users inherit the permissions and privileges of the public role and this role provides the minimum permissions and privileges. Any roles that you assign to a user beyond the public role may add permissions and privileges. If you want all database users to have specific permissions, assign the permissions to the public role.
db_accessadmin. Designed for users who need to add or remove logins in a database.
db_backupoperator. Designed for users who need to back up a database.
db_datareader. Designed for users who need to view data in a database. Members of this role can select all data from any user table in the database.
db_datawriter. Designed for users who need to add or modify any data in any user table in the database. Members of this role can perform these tasks on any objects in the selected database: DELETE, INSERT, and UPDATE.
db_ddladmin. Designed for users who need to perform tasks related to the data definition language (DDL) for SQL Server. Members of this role can issue any DDL statement except GRANT, REVOKE, or DENY.
db_denydatareader. Designed to restrict access to data in a database by login. Members of this role cannot read any data in user tables within a database.
db_denydatawriter. Designed to restrict modifications permissions in a database by login. Members of this role cannot add, modify, or delete any data in user tables within a database.
db_securityadmin. Designed for users who need to manage permissions, object ownership, and roles.
db_owner. Designed for users who need complete control over all aspects of the database. Members of this role can assign permissions, modify database settings, perform database maintenance, and perform any other administration task on the database, including dropping the database.
Fixed database roles can be mapped to the more granular permissions for SQL Server 2005, as shown in Table 8-7.
Table 8-7. Granular Permissions Associated with Fixed Database Roles
Permissions Denied with This Role | |
|---|---|
db_denydatareader | SELECT |
db_denydatawriter | DELETE, INSERT, UPDATE |
db_accessadmin | ALTER ANY USER, CONNECT with GRANT option, CREATE SCHEMA |
db_backupoperator | BACKUP DATABASE, BACKUP LOG, CHECKPOINT |
db_datareader | SELECT |
db_datawriter | DELETE, INSERT, UPDATE |
db_ddladmin | ALTER ANY ASSEMBLY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY EVENT NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, ALTER ANY TRIGGER, ALTER ANY XML SCHEMA COLLECTION, CHECKPOINT, CREATE AGGREGATE, CREATE ASSEMBLY, CREATE CONTRACT, CREATE DEFAULT, CREATE FUNCTION, CREATE MESSAGE TYPE, CREATE PROCEDURE, CREATE QUEUE, CREATE REMOTE SERVICE BINDING, CREATE ROUTE, CREATE RULE, CREATE SCHEMA, CREATE SERVICE, CREATE SYMMETRIC KEY, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES |
db_owner | CONTROL with GRANT option |
db_securityadmin | ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION |