sys.database_permissions (Transact-SQL)

 

Updated: November 29, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns a row for every permission or column-exception permission in the database. For columns, there is a row for every permission that is different from the corresponding object-level permission. If the column permission is the same as the corresponding object permission, there will be no row for it and the actual permission used will be that of the object.

System_CAPS_ICON_important.jpg Important


Column-level permissions override object-level permissions on the same entity.

Column nameData typeDescription
classtinyintIdentifies class on which permission exists.

0 = Database
1 = Object or Column
3 = Schema
4 = Database Principal
5 = Assembly - Applies to: SQL Server 2008 through SQL Server 2016.
6 = Type
10 = XML Schema Collection -
                      Applies to: SQL Server 2008 through SQL Server 2016.
15 = Message Type - Applies to: SQL Server 2008 through SQL Server 2016.
16 = Service Contract - Applies to: SQL Server 2008 through SQL Server 2016.
17 = Service - Applies to: SQL Server 2008 through SQL Server 2016.
18 = Remote Service Binding - Applies to: SQL Server 2008 through SQL Server 2016.
19 = Route - Applies to: SQL Server 2008 through SQL Server 2016.
23 =Full-Text Catalog - Applies to: SQL Server 2008 through SQL Server 2016.
24 = Symmetric Key - Applies to: SQL Server 2008 through SQL Server 2016.
25 = Certificate - Applies to: SQL Server 2008 through SQL Server 2016.
26 = Asymmetric Key - Applies to: SQL Server 2008 through SQL Server 2016.
class_descnvarchar(60)Description of class on which permission exists.

DATABASE

OBJECT_OR_COLUMN

SCHEMA

DATABASE_PRINCIPAL

ASSEMBLY

TYPE

XML_SCHEMA_COLLECTION

MESSAGE_TYPE

SERVICE_CONTRACT

SERVICE

REMOTE_SERVICE_BINDING

ROUTE

FULLTEXT_CATALOG

SYMMETRIC_KEYS

CERTIFICATE

ASYMMETRIC_KEY
major_idintID of thing on which permission exists, interpreted according to class. For most, this is simply the kind of ID that applies to what the class represents. Interpretation for nonstandard is as follows:

0 = Always 0

1 = Object-ID

Negative IDs are assigned to system objects.
minor_idintSecondary-ID of thing on which permission exists, interpreted according to class. For most, this is zero. Otherwise, it is the following:

1 = Column-ID if a column. Otherwise, it is 0 if an object.
grantee_principal_idintDatabase principal ID to which the permissions are granted.
grantor_principal_idintDatabase principal ID of the grantor of these permissions.
typechar(4)Database permission type. For a list of permission types, see the next table.
permission_namenvarchar(128)Permission name.
statechar(1)Permission state:

D = Deny

R = Revoke

G = Grant

W = Grant With Grant Option
state_descnvarchar(60)Description of permission state:

DENY

REVOKE

GRANT

GRANT_WITH_GRANT_OPTION
Permission typePermission nameApplies to securable
ALALTERAPPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, USER, XML SCHEMA COLLECTION
ALAKALTER ANY ASYMMETRIC KEYDATABASE
ALARALTER ANY APPLICATION ROLEDATABASE
ALASALTER ANY ASSEMBLYDATABASE
ALCFALTER ANY CERTIFICATEDATABASE
ALDSALTER ANY DATASPACEDATABASE
ALEDALTER ANY DATABASE EVENT NOTIFICATIONDATABASE
ALFTALTER ANY FULLTEXT CATALOGDATABASE
ALMTALTER ANY MESSAGE TYPEDATABASE
ALRLALTER ANY ROLEDATABASE
ALRTALTER ANY ROUTEDATABASE
ALSBALTER ANY REMOTE SERVICE BINDINGDATABASE
ALSCALTER ANY CONTRACTDATABASE
ALSKALTER ANY SYMMETRIC KEYDATABASE
ALSMALTER ANY SCHEMADATABASE
ALSVALTER ANY SERVICEDATABASE
ALTGALTER ANY DATABASE DDL TRIGGERDATABASE
ALUSALTER ANY USERDATABASE
AUTHAUTHENTICATEDATABASE
BADBBACKUP DATABASEDATABASE
BALOBACKUP LOGDATABASE
CLCONTROLAPPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION
COCONNECTDATABASE
CORPCONNECT REPLICATIONDATABASE
CPCHECKPOINTDATABASE
CRAGCREATE AGGREGATEDATABASE
CRAKCREATE ASYMMETRIC KEYDATABASE
CRASCREATE ASSEMBLYDATABASE
CRCFCREATE CERTIFICATEDATABASE
CRDBCREATE DATABASEDATABASE
CRDFCREATE DEFAULTDATABASE
CREDCREATE DATABASE DDL EVENT NOTIFICATIONDATABASE
CRFNCREATE FUNCTIONDATABASE
CRFTCREATE FULLTEXT CATALOGDATABASE
CRMTCREATE MESSAGE TYPEDATABASE
CRPRCREATE PROCEDUREDATABASE
CRQUCREATE QUEUEDATABASE
CRRLCREATE ROLEDATABASE
CRRTCREATE ROUTEDATABASE
CRRUCREATE RULEDATABASE
CRSBCREATE REMOTE SERVICE BINDINGDATABASE
CRSCCREATE CONTRACTDATABASE
CRSKCREATE SYMMETRIC KEYDATABASE
CRSMCREATE SCHEMADATABASE
CRSNCREATE SYNONYMDATABASE
CRSOApplies to: SQL Server 2012 through SQL Server 2016.

CREATE SEQUENCE
DATABASE
CRSVCREATE SERVICEDATABASE
CRTBCREATE TABLEDATABASE
CRTYCREATE TYPEDATABASE
CRVWCREATE VIEWDATABASE
CRXSApplies to: SQL Server 2008 through SQL Server 2016.

CREATE XML SCHEMA COLLECTION
DATABASE
DLDELETEDATABASE, OBJECT, SCHEMA
EXEXECUTEASSEMBLY, DATABASE, OBJECT, SCHEMA, TYPE, XML SCHEMA COLLECTION
IMIMPERSONATEUSER
ININSERTDATABASE, OBJECT, SCHEMA
RCRECEIVEOBJECT
RFREFERENCESASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, SCHEMA, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTION
SLSELECTDATABASE, OBJECT, SCHEMA
SNSENDSERVICE
SPLNSHOWPLANDATABASE
SUQNSUBSCRIBE QUERY NOTIFICATIONSDATABASE
TOTAKE OWNERSHIPASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTION
UPUPDATEDATABASE, OBJECT, SCHEMA
VWVIEW DEFINITIONAPPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION
VWCTVIEW CHANGE TRACKINGTABLE, SCHEMA
VWDSVIEW DATABASE STATEDATABASE

Any user can see their own permissions. To see permissions for other users, requires VIEW DEFINITION, ALTER ANY USER, or any permission on a user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role (such as public).

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

A: Listing all the permissions of database principals

The following query lists the permissions explicitly granted or denied to database principals.

System_CAPS_ICON_important.jpg Important


The permissions of fixed database roles do not appear in sys.database_permissions. Therefore, database principals may have additional permissions not listed here.

SELECT pr.principal_id, pr.name, pr.type_desc,   
    pr.authentication_type_desc, pe.state_desc, pe.permission_name  
FROM sys.database_principals AS pr  
JOIN sys.database_permissions AS pe  
    ON pe.grantee_principal_id = pr.principal_id;  

B: Listing permissions on schema objects within a database

The following query joins sys.database_principals and sys.database_permissions to sys.objects and sys.schemas to list permissions granted or denied to specific schema objects.

SELECT pr.principal_id, pr.name, pr.type_desc,   
    pr.authentication_type_desc, pe.state_desc,   
    pe.permission_name, s.name + '.' + o.name AS ObjectName  
FROM sys.database_principals AS pr  
JOIN sys.database_permissions AS pe  
    ON pe.grantee_principal_id = pr.principal_id  
JOIN sys.objects AS o  
    ON pe.major_id = o.object_id  
JOIN sys.schemas AS s  
    ON o.schema_id = s.schema_id;  

C: Listing all the permissions of database principals

The following query lists the permissions explicitly granted or denied to database principals.

System_CAPS_ICON_important.jpg Important


The permissions of fixed database roles do not appear in sys.database_permissions. Therefore, database principals may have additional permissions not listed here.

SELECT pr.principal_id, pr.name, pr.type_desc,   
    pr.authentication_type_desc, pe.state_desc, pe.permission_name  
FROM sys.database_principals AS pr  
JOIN sys.database_permissions AS pe  
    ON pe.grantee_principal_id = pr.principal_id;  

D: Listing permissions on schema objects within a database

The following query joins sys.database_principals and sys.database_permissions to sys.objects and sys.schemas to list permissions granted or denied to specific schema objects.

SELECT pr.principal_id, pr.name, pr.type_desc,   
    pr.authentication_type_desc, pe.state_desc,   
    pe.permission_name, s.name + '.' + o.name AS ObjectName  
FROM sys.database_principals AS pr  
JOIN sys.database_permissions AS pe  
    ON pe.grantee_principal_id = pr.principal_id  
JOIN sys.objects AS o  
    ON pe.major_id = o.object_id  
JOIN sys.schemas AS s  
    ON o.schema_id = s.schema_id;  

Securables
Permissions Hierarchy (Database Engine)
Security Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)

Community Additions

ADD
Show: