This topic describes how to determine who has permissions to various objects in the SQL Server Database Engine. SQL Server implements two permission systems for the Database Engine. An older system of fixed roles has preconfigured permissions. Beginning with SQL Server 2005 a more flexible and precise system is available. (The information in this topic applies to SQL Server, beginning with 2005. Some types of permissions are not available in some versions of SQL Server.)
Important:
Older Fixed Role Permission System
Fixed Server Roles and Fixed Database Roles have preconfigured permissions that cannot be changed. To determine who is a member of a fixed server role, execute the following query. Note: The is_fixed_role column of sys.server_principals was added in SQL Server 2012. It is not needed for older versions of SQL Server.
SELECT SP1.name AS ServerRoleName, isnull (SP2.name, 'No members') AS LoginName --SP2.name AS LoginName FROM sys.server_role_members AS SRM RIGHT OUTER JOIN sys.server_principals AS SP1 ON SRM.role_principal_id = SP1.principal_id LEFT OUTER JOIN sys.server_principals AS SP2 ON SRM.member_principal_id = SP2.principal_id WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008 ORDER BY SP1.name;
Note:
To determine who is a member of a fixed database role, execute the following query in each database.
SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.is_fixed_role = 1 ORDER BY DP1.name;
To understand the permissions that are granted to each role, see the role descriptions in Books Online (Server-Level Roles, Database-Level Roles) and the illustrations at Database Engine Fixed Server and Fixed Database Roles.
This system is extremely flexible, which means it can be complicated if the people setting it up want to be very precise. That's not necessarily bad; I hope my financial institution is precise. To simplify matters it helps to create roles, assign permissions to roles, and then add groups of people to the roles. And it's easier if the database development team separates activity by schema and then grants role permissions to a whole schema instead of to individual tables or procedures. But the real world is complex and we have to assume that business needs create unexpected security requirements.
Permissions can be granted at the server-level, the database-level, the schema-level, or the object-level, etc. There are 25 levels (called classes). The complete list of classes in alphabetic order is: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, ENDPOINT, FULLTEXT CATALOG, FULLTEXT STOPLIST, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SEARCH PROPERTY LIST, SERVER, SERVER ROLE, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION. To provide full information about each class requires a different query.
Permissions are granted to principals. Principals can be server roles, logins, database roles, or users. Logins can represent Windows groups that include many Windows users. Since Windows groups are not maintained by SQL Server, SQL Server does not always know who is a member of a Windows group. When a Windows user connects to SQL Server, the login packet contains the Windows group membership tokens for the user.
When a Windows user connects using a login based on a Windows group, some activities may require SQL Server to create a login or user to represent the individual Windows user. For example, a Windows group (Engineers) contains users (Mary, Todd, Pat) and the Engineers group has a login. If Mary has permission and creates a table, a user (Mary) might be created to be the owner of the table. Or if Todd is denied a permission that the rest of the Engineers group has, then the user Todd must be created to track the permission denial.
Remember that a Windows user might be a member of more than one Windows group (e.g. both Engineers, and Managers). Permissions granted or denied to the Engineers login, to the Managers login, granted or denied to the user individually, and granted or denied to roles that the user is a member of, will all be aggregated and evaluated to for the effective permissions. The HAS_PERMS_BY_NAME function can reveal whether a user or login has a particular permission. However, there is no obvious way of determining the source of the grant or denial of permission. You must study the list of permissions and perhaps experiment using trial and error.
The following query returns a list of the permissions that have been granted or denied at the server level. This query should be executed in the master database.
SELECT pr.type_desc, pr.name, isnull (pe.state_desc, 'No permission statements') AS state_desc, isnull (pe.permission_name, 'No permission statements') AS permission_name FROM sys.server_principals AS pr LEFT OUTER JOIN sys.server_permissions AS pe ON pr.principal_id = pe.grantee_principal_id WHERE is_fixed_role = 0 -- Remove for SQL Server 2008 ORDER BY pr.name, type_desc;
The following query returns a list of the permissions that have been granted or denied at the database level. This query should be executed in each database.
SELECT pr.type_desc, pr.name, isnull (pe.state_desc, 'No permission statements') AS state_desc, isnull (pe.permission_name, 'No permission statements') AS permission_name FROM sys.database_principals AS pr LEFT OUTER JOIN sys.database_permissions AS pe ON pr.principal_id = pe.grantee_principal_id WHERE pr.is_fixed_role = 0 ORDER BY pr.name, type_desc;
Each class of permission the permission table can be joined to other system views that provide related information about that class of securable. For example, the following query provides the name of the database object that is affected by the permission.
SELECT pr.type_desc, pr.name, pe.state_desc, pe.permission_name, s.name + '.' + oj.name AS Object, major_id FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe ON pr.principal_id = pe.grantee_principal_id JOIN sys.objects AS oj ON oj.object_id = pe.major_id JOIN sys.schemas AS s ON oj.schema_id = s.schema_id WHERE class_desc = 'OBJECT_OR_COLUMN';
Use the HAS_PERMS_BY_NAME function to determine if a particular user has a permission. For example:
EXECUTE AS USER = 'TestUser'; SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT'); REVERT;
See Books Online HAS_PERMS_BY_NAME for the details of the syntax.
Rick Byham, Microsoft edited Revision 4. Comment: Added the note about is_fixed_role being version specific.
Rick Byham, Microsoft edited Revision 3. Comment: Changing Summary to H2
Rick Byham, Microsoft edited Revision 2. Comment: Added the summary
Nice post
gracias