In SQL Server 2008 R2, Database Engine permissions are managed at the server level through logins and fixed server roles, and at the database level through database users, fixed database roles, and user-defined database roles. Download the Permissions Poster PDF file.
Table of Contents LoginsFixed Server RolesDatabase UsersFixed Database RolesUser-defined Database RolesTypical ScenarioAssigning PermissionsPermission HierarchyGrant the Least PermissionsList of PermissionsPermissions vs. Fixed Server and Fixed Database RolesMonitoring Permissions
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 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.
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 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.
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.
The following example represents a common and recommended method of configuring permissions.
In Active Directory:
In SQL Server:
Most permission statements have the format :
AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL
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.
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 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).
So SELECT permission on the Region table can be achieved through any of these statements:
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. It's a good principal to always grant the least permission possible, but (contradicting that) 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.
SQL Server 2012 has 214 permissions. SQL Server 2008 R2 has 195 permissions. Windows Azure SQL Database has fewer permissions because most server level permissions are not applicable. 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. Download the Database Engine Permissions Poster:
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.
The following views return security information.
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.
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;
Nominating this article to be featured. Great job!
Great job on the resources, including the easy to read poster Rick!
I'm going to send this poster to the A1 plotter (once everyone's gone home) :)
+1 for printing the poster :)
Great article. Thanks for sharing.
Thanks for the information. Through all of the reading I have done on this very topic, I have never been able to figure out the permission scheme of SQL Server. You have taken me a huge step in the right direction. I really appreciate it. But why are their more permissions in SQL Server 2012? Aren't you doing away with the concept of ligins in that version?
There are new permissions in SQL Server 2012 because there are new features, like Availability Groups, and they need permissions. No, SQL Server 2012 does not do away with logins, though the new "Contained Database Users" feature does not require logins. I think logins will be with us for a long time for backward compatibility.
I believe that are some new permissions in new SQL Server, im trying understand this new ones, that came with the new features. But your post are cleaning my mind, i was stock needing one permission for days. Thankz for sharing
A good overview which should explain the whole concept altogether for anyone.
I would like to add something which belongs to the part “Permissions vs. Fixed Server and Fixed Database Roles”, where it is said: … making adding a login to the sysadmin fixed server role does not explicitly grant the login the CONTROL SERVER permission…
This is another nice example of where the difference between “effective permissions” and “explicitly granted permissions” can be seen. The Functions sys.fn_my_permissions and HAS_PERMS_BY_NAME() both return the effective permissions – including CONTROL SERVER - , whereas the DMV sys.server_permissions returns only the explicitely granted permissions – nothing but CONNECT SQL for a sysadmin by default.
Some more on the differences between CONTROL SERVER and sysadmin can be found in this blog post: www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats