I am posting this on behalf of my colleague Rick Byham, a technical writer on the SQL Server Team.
Database Engine permissions are managed at the server level through logins and fixed server roles, and at the database level through database users and user-defined database roles.
Logins
Logins are individual user accounts for logging on to the SQL Server Database Engine. SQL Server supports logins based on Windows authentication and logins based on SQL Server authentication. For information about the two types of logins, see
Choosing an Authentication Mode
Fixed Server Roles
Fixed server roles are a set of preconfigured roles that provide convenient group of server-level permissions. Logins can be added to the roles using the
sp_addsrvrolemember
procedure.
Database Users
Logins are granted access to a database by creating a database user in a database and mapping that database user to login. Typically the database user name is the same as the login name, though it does not have to be the same. Each database user maps to a single login. A login can be mapped to only one user in a database, but can be mapped as a database user in several different databases.
Fixed Database Roles
Fixed database roles are a set of preconfigured roles that provide convenient group of database-level permissions. Database users and user-defined database roles can be added to the fixed database roles using the
sp_addrolemember
procedure.
User-defined Database Roles
Users with the
CREATE ROLE
permission can create new user-defined database roles to represent groups of users with common permissions. Typically permissions are granted or denied to the entire role, simplifying permissions management and monitoring.
Typical Scenario
The following example represents a common and recommended method of configuring permissions.
In Active Directory:
- Create a Windows user for each person.
- Create Windows groups that represent the work units and the work functions.
- Add the Windows users to the Windows groups.
In SQL Server:
- Create a login for the Windows groups. (If using SQL Server authentication, skip the Active Directory steps, and create SQL Server authentication logins here.)
- Create a database user for the login representing the Windows groups.
- Create one or more user-defined database roles, each representing a similar function. For example financial analyst, and sales analyst.
- Add database users to one or more user-defined database roles.
- Grant permissions to the user-defined database roles.
Assigning Permissions
Most permission statements have the format :
AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL
- AUTHORIZATION must be GRANT, REVOKE or DENY.
- PERMISSION is listed in the chart referenced below.
- ON SECURABLE::NAME is the server, server object, database, or database object and its name. Some permissions do not require ON SECURABLE::NAME because it is unambiguous or inappropriate in the context. For example the CREATE TABLE permission doesn’t require the ON SECURABLE::NAME clause.
- PRINCIPAL is the login, user, or role which receives or loses the permission. Grant permissions to roles whenever possible.
Sample grant statement: GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam
Permissions are granted to security principals (logins, users, and roles) by using the
GRANT
statement. Permissions are explicitly denied by using the
DENY
command. A previously granted or denied permission is removed by using the
REVOKE
statement. Permissions are cumulative, with the user receiving all the permissions granted to the user, login, and any group memberships; however any permission denial overrides all grants.
Tip:
A common mistake is to attempt to remove a
GRANT
by using
DENY
instead of
REVOKE
. This can cause problems when a user receives permissions from multiple sources; which is quite common. The following example demonstrates the principal.
The
Sales
group receives
SELECT
permissions on the
OrderStatus
table through the statement
GRANT SELECT ON OBJECT::OrderStatus TO Sales.
User
Ted
is a member of the
Sales
role. Ted has also been granted
SELECT
permission to the
OrderStatus
table under his own user name through the statement
GRANT SELECT ON OBJECT::OrderStatus TO Ted
. Presume the administer wishes to remove the
GRANT
to the
Sales
role.
- If the administrator correctly executes REVOKE SELECT ON OBJECT::OrderStatus TO Sales , then Ted will retain SELECT access to the OrderStatus table through his individual GRANT statement.
- If the administrator incorrectly executes DENY SELECT ON OBJECT::OrderStatus TO Sales then Ted , as a member of the Sales role, will be denied the SELECT permission because the DENY to Sales overrides his individual GRANT .
Permission Hierarchy
Permissions have a parent/child hierarchy. That is, if you grant
SELECT
permission on a database, if includes
SELECT
permission on all (child) schemas in the database. If you grant
SELECT
permission on a schema, it includes
SELECT
permission on all the (child) tables and views in the schema. The permissions are transitive; that is, if you grant
SELECT
permission on a database, it includes
SELECT
permission on all (child) schemas, and all (grandchild) tables, and all views.
Permissions also have covering permissions. The
CONTROL
permission on an object, normally gives you all other permissions on the object.
Because both the parent/child hierarchy and the covering hierarchy can act on the same permission, the permission system can get complicated. For example, let's take a table (
Region
), in a schema (
Customers
), in a database (
SalesDB
).
- CONTROL permission on table Region includes all the other permissions on the table Region , including ALTER , SELECT , INSERT , UPDATE , DELETE , and some other permissions.
- SELECT on the Customers schema that owns the Region table includes the SELECT permission on the Region table.
So
SELECT
permission on the
Region
table can be achieved through any of these three statements:
- GRANT SELECT ON OBJECT::Region TO Ted
- GRANT CONTROL ON OBJECT::Region TO Ted
- GRANT SELECT ON SCHEMA::Customers TO Ted
- GRANT CONTROL ON SCHEMA::Customers TO Ted
- GRANT SELECT ON DATABASE::SalesDB TO Ted
- GRANT CONTROL ON DATABASE::SalesDB TO Ted
Grant the Least Permissions
The first permission listed above (
GRANT SELECT ON OBJECT::Region TO Ted
) is the most granular, that is, that statement is the least permission possible that grants the
SELECT
. No permissions to subordinate objects come with it. Always grant the least permission possible, but grant at higher levels in order to simplify the granting system. So if Ted needs permissions to the entire schema, grant
SELECT
once at the schema level, instead of granting
SELECT
at the table of view level many times. The design of the database has a great deal of impact on how successful this strategy can be. This strategy will work best when your database is designed so that objects needing identical permissions are included in a single schema.
List of Permissions
SQL Server 2008 R2 has 195 permissions. SQL Server Code-named 'Denali' has 214 permissions. The following graphic shows the permissions and their relationships to each other. Some of the higher level permissions (such as
CONTROL SERVER
) are listed many times.
5710.Permissions_Poster_2008_R2_Wiki.pdf
Permissions vs. Fixed Server and Fixed Database Roles
The permissions of the fixed server roles and fixed database roles are similar but not exactly the same as the granular permissions. For example, members of the
sysadmin
fixed server role have all permissions on the instance of SQL Server, as do logins with the
CONTROL SERVER
permission. But granting the
CONTROL SERVER
permission does not make a login a member of the
sysadmin
fixed server role, and making adding a login to the
sysadmin
fixed server role does not explicitly grant the login the
CONTROL SERVER
permission. Sometimes a stored procedure will check permissions by checking the fixed role and not checking the granular permission. For example detaching a database requires membership in the
db_owner
fixed database role. The equivalent
CONTROL DATABASE
permission is not enough. These two systems operate in parallel but rarely interact with each other. Microsoft recommends using the newer, granular permission system instead of the fixed roles whenever possible.
Monitoring permissions
The following views return security information.
- The logins and user-defined server roles (available in SQL Server Code-named 'Denali') on a server can be examined by using the sys.server_principals view.
- The users and user-defined roles in a database can be examined by using the sys.database_principals view.
- The permissions granted to logins and user-defined fixed server roles can be examined by using the sys.server_permissions view.
- The permissions granted to user and user-defined fixed database roles can be examined by using the sys.database_permissions view.
- Database role membership can be examined by using the sys. sys.database_role_members view.
- Server role membership can be examined by using the sys. sys.server_role_members view.
-
For additional security related views, see
Security Catalog Views (Transact-SQL)
.
The following statements return useful information about permissions.
To return the explicit permissions granted or denied in a database, execute the following statement in the database.
SELECT
perms.state_desc AS State,
permission_name AS [Permission],
obj.name AS [on Object],
dPrinc.name AS [to User Name],
sPrinc.name AS [who is Login Name]
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS dPrinc
ON perms.grantee_principal_id = dPrinc.principal_id
JOIN sys.objects AS obj
ON perms.major_id = obj.object_id
LEFT OUTER JOIN sys.server_principals AS sPrinc
ON dPrinc.sid = sPrinc.sid
To return the members of the server roles, execute the following statement.
SELECT sRole.name AS [Server Role Name] , sPrinc.name AS [Members]
FROM sys.server_role_members AS sRo
JOIN sys.server_principals AS sPrinc
ON sRo.member_principal_id = sPrinc.principal_id
JOIN sys.server_principals AS sRole
ON sRo.role_principal_id = sRole.principal_id;
To return the members of the database roles, execute the following statement in the database.
SELECT dRole.name AS [Database Role Name], dPrinc.name AS [Members]
FROM sys.database_role_members AS dRo
JOIN sys.database_principals AS dPrinc
ON dRo.member_principal_id = dPrinc.principal_id
JOIN sys.database_principals AS dRole
ON dRo.role_principal_id = dRole.principal_id;