When several users need to perform similar activities in a particular database (and there is no corresponding Windows group), you can add a database role, which specifies a group of database users that can access the same objects of the database.
Members of a database role can be any of the following:
- Windows groups and user accounts
- SQL Server logins
- Other roles
The security architecture in the Database Engine includes several “system” roles that have special implicit permissions. There are two types of predefined roles (in addition to user-defined roles):
- Fixed server roles
- Fixed database roles
Beside these two, the following sections also describe the following types of roles:
- Application roles
- User-defined server roles
- User-defined database roles
The following sections describe in detail these role types.
Fixed Server Roles
Fixed server roles are defined at the server level and therefore exist outside of databases belonging to the database server. Table 12-1 lists all existing fixed server roles.
Table 12-1 Fixed Server Roles
|Fixed Server Role||Description|
|sysadmin||Performs any activity in the database system|
|serveradmin||Configures server settings|
|setupadmin||Installs replication and manages extended procedures|
|securityadmin||Manages logins and CREATE DATABASE permissions and reads audits|
|processadmin||Manages system processes|
|dbcreator||Creates and modifies databases|
|diskadmin||Manages disk files|
Managing Fixed Server Roles
You can add members to and delete members from a fixed server roles in two ways:
- Using Management Studio
- Using T-SQL statements
To add a login to a fixed server role using SQL Server Management Studio, expand the server, expand Security, and expand Server Roles. Right-click the role to which you want to add a login and then click Properties. On the Members page of the Server Role Properties dialog box (see Figure 12-4), click Add. Search for the login you want to add. Such a login is then a member of the role and inherits all credentials assigned to that role.
The Transact-SQL statements CREATE SERVER ROLE and DROP SERVER ROLE are used, respectively, to add members to and delete members from a fixed server role. There ALTER SERVER ROLE statement modifies the membership of a server role. Example 12.9, in section “User-Defined Server Roles”, shows the use of the CREATE SERVER ROLE and ALTER SERVER ROLE statements.
The sa Login
The sa login is the login of the system administrator. In versions previous to SQL Server 2005, in which roles did not exist, the sa login was granted all possible permissions for system administration tasks. Now, the sa login is included just for backward compatibility. This login is always a member of the sysadmin fixed server role and cannot be removed from the role.
Fixed Database Roles
Fixed database roles are defined at the database level and therefore exist in each database belonging to the database server. Table 12-2 lists all of the fixed database roles. Members of the fixed database role can perform different activities. Use Books Online to learn which activities are allowed for each of the fixed database roles.
Table 12-2 Fixed Database Roles
|Fixed Database Role||Description|
|db_owner||Users who can perform almost all activities in the database|
|db_accessadmin||Users who can add or remove users|
|db_datareader||Users who can see data from all user tables in the database|
|db_datawriter||Users who can add, modify, or delete data in all user tables in the database|
|db_ddladmin||Users who can perform all DDL operations in the database|
|db_securityadmin||Users who can manage all activities concerning security permissions in the database|
|db_backupoperator||Users who can back up the database|
|db_denydatareader||Users who cannot see any data in the database|
|db_denydatawriter||Users who cannot change any data in the database|
Besides the fixed database roles listed in Table 12-2, there is a special fixed database role called public, which is explained next.
The public role is a special fixed database role to which every legitimate user of a database belongs. It captures all default permissions for users in a database. This provides a mechanism for giving all users without appropriate permissions a set of (usually limited) permissions. The public role maintains all default permissions for users in a database and cannot be dropped. This role cannot have users, groups, or roles assigned to it because they belong to the role by default. (Example 12.19, in section “Authorization”, shows the use of the public role.)
By default, the public role allows users to do the following:
- View system tables and display information from the master system database using certain system procedures
- Execute statements that do not require permissions—for example, PRINT
Assigning a User to a Fixed Database Role
To assign a user to a fixed database role using SQL Server Management Studio, expand the server, expand Databases, expand the database, expand Security, expand Roles, and then expand Database Roles. Right-click the role to which you want to add a user and then click Properties. In the Database Role Properties dialog box, click Add and browse for the user(s) you want to add. Such an account is then a member of the role and inherits all credentials assigned to that role.