Roles are a powerful tool that allow you to collect users into a single unit against which you can apply permissions. Permissions granted to, denied to, or revoked from a role also apply to any members of the role. You can establish a role that represents a job performed by a class of workers in your organization and grant the appropriate permissions to that role. As workers rotate into the job, you simply add them as a member of the role; as they rotate out of the job, remove them from the role. You do not have to repeatedly grant, deny, and revoke permissions to or from each person as they accept or leave the job. The permissions are applied automatically when the users become members of the role.
Windows NT groups can be used in much the same way as roles. For more information, see Groups.
It is easy to manage the permissions in a database if you define a set of roles based on job functions and assign each role the permissions that apply to that job. You can then simply move users between roles rather than having to manage the permissions for each individual user. If the function of a job changes, it is easier to simply change the permissions once for the role and have the changes applied automatically to all members of the role.
In Microsoft® SQL Server™ version 7.0, users can belong to multiple roles.
The following script shows adding a few logins, users, and roles, and granting permissions to the roles.
USE master
GO
sp_grantlogin ‘NETDOMAIN\John’
GO
sp_defaultdb 'NETDOMAIN\John', ‘courses’
GO
sp_grantlogin ‘NETDOMAIN\Sarah’
GO
sp_defaultdb 'NETDOMAIN\Sarah', 'courses'
GO
sp_grantlogin ‘NETDOMAIN\Betty’
GO
sp_defaultdb 'NETDOMAIN\Betty', ‘courses’
GO
sp_grantlogin ‘NETDOMAIN\Ralph’
GO
sp_defaultdb 'NETDOMAIN\Ralph', ‘courses’
GO
sp_grantlogin ‘NETDOMAIN\Diane’
GO
sp_defaultdb 'NETDOMAIN\Diane', ‘courses’
GO
USE courses
GO
sp_grantdbaccess ‘NETDOMAIN\John’
GO
sp_grantdbaccess ‘NETDOMAIN\Sarah’
GO
sp_grantdbaccess ‘NETDOMAIN\Betty’
GO
sp_grantdbaccess ‘NETDOMAIN\Ralph’
GO
sp_grantdbaccess ‘NETDOMAIN\Diane’
GO
sp_addrole 'Professor'
GO
sp_addrole 'Student'
GO
sp_addrolemember 'Professor', ‘NETDOMAIN\John’
GO
sp_addrolemember 'Professor', ‘NETDOMAIN\Sarah’
GO
sp_addrolemember 'Professor', ‘NETDOMAIN\Diane’
GO
sp_addrolemember 'Student', ‘NETDOMAIN\Betty’
GO
sp_addrolemember 'Student', ‘NETDOMAIN\Ralph’
GO
sp_addrolemember 'Student', ‘NETDOMAIN\Diane’
GO
GRANT SELECT ON StudentGradeView TO Student
GO
GRANT SELECT, UPDATE ON ProfessorGradeView TO Professor
GO
This script gives the professors John and Sarah permission to update students grades, while the students Betty and Ralph can only select their grades. Diane has been added to both roles because she is teaching one class while taking another. The view ProfessorGradeView should restrict professors to the rows for students in their classes, while StudentGradeView should restrict students to selecting only their own grades.
There are several fixed roles defined in SQL Server 7.0 during setup. Users can be added to these roles to pick up the associated administration permissions. These are serverwide roles.
| Fixed server role | Description |
|---|---|
| sysadmin | Can perform any activity in SQL Server |
| serveradmin | Can set serverwide configuration options, shut down the server |
| setupadmin | Can manage linked servers and startup procedures |
| securityadmin | Can manage logins and CREATE DATABASE permissions, also read error logs |
| processadmin | Can manage processes running in SQL Server |
| dbcreator | Can create and alter databases |
| diskadmin | Can manage disk files |
You can get a list of the fixed server roles from sp_helpsrvrole, and get the specific permissions for each role from sp_srvrolepermission.
Each database has a set of fixed database roles. While roles with the same names exist in each database, the scope of an individual role is only within a specific database. For example, if Database1 and Database2 both have user IDs named UserX, adding UserX in Database1 to the db_owner fixed database role for Database1 has no effect on whether UserX in Database2 is a member of the db_owner role for Database2.
| Fixed database role | Description |
|---|---|
| db_owner | Has all permissions in the database |
| db_accessadmin | Can add or remove user IDs |
| db_securityadmin | Can manage all permissions, object ownerships, roles and role memberships |
| db_ddladmin | Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements |
| db_backupoperator | Can issue DBCC, CHECKPOINT, and BACKUP statements |
| db_datareader | Can select all data from any user table in the database |
| db_datawriter | Can modify any data in any user table in the database |
| db_denydatareader | Can deny or revoke SELECT permissions on any object |
| db_denydatawriter | Can deny or revoke INSERT, UPDATE, and DELETE permissions on any object |
You can get a list of the fixed database roles from sp_helpdbfixedrole, and get the specific permissions for each role from sp_dbfixedrolepermission.
Every user in a database belongs to the public database role. If you want everyone in a database to be able to have a specific permission, assign the permission to the public role. If a user has not been specifically granted permissions on an object, they use the permissions assigned to public.
| Adding a Member to a Predefined Role | sp_helpsrvrole |
| sp_dbfixedrolepermission | sp_srvrolepermission |
| sp_helpdbfixedrole |