sys.database_permissions (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
SQL database in Microsoft Fabric
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 is no row for it and the permission applied is that of the object.
Important
Column-level permissions override object-level permissions on the same entity.
| Column name | Data type | Description |
|---|---|---|
| class | tinyint | Identifies class on which permission exists. For more information, see sys.securable_classes (Transact-SQL). 0 = Database 1 = Object or Column 3 = Schema 4 = Database Principal 5 = Assembly - Applies to: SQL Server 2008 (10.0.x) and later versions. 6 = Type 10 = XML Schema Collection - Applies to: SQL Server 2008 (10.0.x) and later versions. 15 = Message Type - Applies to: SQL Server 2008 (10.0.x) and later versions. 16 = Service Contract - Applies to: SQL Server 2008 (10.0.x) and later versions. 17 = Service - Applies to: SQL Server 2008 (10.0.x) and later versions. 18 = Remote Service Binding - Applies to: SQL Server 2008 (10.0.x) and later versions. 19 = Route - Applies to: SQL Server 2008 (10.0.x) and later versions. 23 =Full-Text Catalog - Applies to: SQL Server 2008 (10.0.x) and later versions. 24 = Symmetric Key - Applies to: SQL Server 2008 (10.0.x) and later versions. 25 = Certificate - Applies to: SQL Server 2008 (10.0.x) and later versions. 26 = Asymmetric Key - Applies to: SQL Server 2008 (10.0.x) and later versions. 29 = Fulltext Stoplist - Applies to: SQL Server 2008 (10.0.x) and later versions. 31 = Search Property List - Applies to: SQL Server 2008 (10.0.x) and later versions. 32 = Database Scoped Credential - Applies to: SQL Server 2016 (13.x) and later versions. 34 = External Language - Applies to: SQL Server 2019 (15.x) and later versions. |
| class_desc | nvarchar(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 FULLTEXT STOPLIST SEARCH PROPERTY LIST DATABASE SCOPED CREDENTIAL EXTERNAL LANGUAGE |
| major_id | int | ID of thing on which permission exists, interpreted according to class. Usually, the major_id simply the kind of ID that applies to what the class represents. 0 = The database itself >0 = Object-IDs for user objects <0 = Object-IDs for system objects |
| minor_id | int | Secondary-ID of thing on which permission exists, interpreted according to class. Often, the minor_id is zero, because there is no subcategory available for the class of object. Otherwise, it is the Column-ID of a table. |
| grantee_principal_id | int | Database principal ID to which the permissions are granted. |
| grantor_principal_id | int | Database principal ID of the grantor of these permissions. |
| type | char(4) | Database permission type. For a list of permission types, see the next table. |
| permission_name | nvarchar(128) | Permission name. |
| state | char(1) | Permission state: D = Deny R = Revoke G = Grant W = Grant With Grant Option |
| state_desc | nvarchar(60) | Description of permission state: DENY REVOKE GRANT GRANT_WITH_GRANT_OPTION |
Database Permissions
The following types of permissions are possible.
| Permission type | Permission name | Applies to securable |
|---|---|---|
| AADS | ALTER ANY DATABASE EVENT SESSION | DATABASE |
| AAMK | ALTER ANY MASK | DATABASE |
| AEDS | ALTER ANY EXTERNAL DATA SOURCE | DATABASE |
| AEFF | ALTER ANY EXTERNAL FILE FORMAT | DATABASE |
| AL | ALTER | APPLICATION 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 |
| ALAK | ALTER ANY ASYMMETRIC KEY | DATABASE |
| ALAR | ALTER ANY APPLICATION ROLE | DATABASE |
| ALAS | ALTER ANY ASSEMBLY | DATABASE |
| ALCF | ALTER ANY CERTIFICATE | DATABASE |
| ALDS | ALTER ANY DATASPACE | DATABASE |
| ALED | ALTER ANY DATABASE EVENT NOTIFICATION | DATABASE |
| ALFT | ALTER ANY FULLTEXT CATALOG | DATABASE |
| ALMT | ALTER ANY MESSAGE TYPE | DATABASE |
| ALRL | ALTER ANY ROLE | DATABASE |
| ALRT | ALTER ANY ROUTE | DATABASE |
| ALSB | ALTER ANY REMOTE SERVICE BINDING | DATABASE |
| ALSC | ALTER ANY CONTRACT | DATABASE |
| ALSK | ALTER ANY SYMMETRIC KEY | DATABASE |
| ALSM | ALTER ANY SCHEMA | DATABASE |
| ALSV | ALTER ANY SERVICE | DATABASE |
| ALTG | ALTER ANY DATABASE DDL TRIGGER | DATABASE |
| ALUS | ALTER ANY USER | DATABASE |
| AUTH | AUTHENTICATE | DATABASE |
| BADB | BACKUP DATABASE | DATABASE |
| BALO | BACKUP LOG | DATABASE |
| CL | CONTROL | APPLICATION 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 |
| CO | CONNECT | DATABASE |
| CORP | CONNECT REPLICATION | DATABASE |
| CP | CHECKPOINT | DATABASE |
| CRAG | CREATE AGGREGATE | DATABASE |
| CRAK | CREATE ASYMMETRIC KEY | DATABASE |
| CRAS | CREATE ASSEMBLY | DATABASE |
| CRCF | CREATE CERTIFICATE | DATABASE |
| CRDB | CREATE DATABASE | DATABASE |
| CRDF | CREATE DEFAULT | DATABASE |
| CRED | CREATE DATABASE DDL EVENT NOTIFICATION | DATABASE |
| CRFN | CREATE FUNCTION | DATABASE |
| CRFT | CREATE FULLTEXT CATALOG | DATABASE |
| CRMT | CREATE MESSAGE TYPE | DATABASE |
| CRPR | CREATE PROCEDURE | DATABASE |
| CRQU | CREATE QUEUE | DATABASE |
| CRRL | CREATE ROLE | DATABASE |
| CRRT | CREATE ROUTE | DATABASE |
| CRRU | CREATE RULE | DATABASE |
| CRSB | CREATE REMOTE SERVICE BINDING | DATABASE |
| CRSC | CREATE CONTRACT | DATABASE |
| CRSK | CREATE SYMMETRIC KEY | DATABASE |
| CRSM | CREATE SCHEMA | DATABASE |
| CRSN | CREATE SYNONYM | DATABASE |
| CRSO | Applies to: SQL Server 2012 (11.x) and later versions. CREATE SEQUENCE |
DATABASE |
| CRSV | CREATE SERVICE | DATABASE |
| CRTB | CREATE TABLE | DATABASE |
| CRTY | CREATE TYPE | DATABASE |
| CRVW | CREATE VIEW | DATABASE |
| CRXS | Applies to: SQL Server 2008 (10.0.x) and later versions. CREATE XML SCHEMA COLLECTION |
DATABASE |
| DABO | ADMINISTER DATABASE BULK OPERATIONS | DATABASE |
| DL | DELETE | DATABASE, OBJECT, SCHEMA |
| EAES | EXECUTE ANY EXTERNAL SCRIPT | DATABASE |
| EX | EXECUTE | ASSEMBLY, DATABASE, OBJECT, SCHEMA, TYPE, XML SCHEMA COLLECTION |
| IM | IMPERSONATE | USER |
| IN | INSERT | DATABASE, OBJECT, SCHEMA |
| RC | RECEIVE | OBJECT |
| RF | REFERENCES | ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, SCHEMA, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTION |
| SL | SELECT | DATABASE, OBJECT, SCHEMA |
| SN | SEND | SERVICE |
| SPLN | SHOWPLAN | DATABASE |
| SUQN | SUBSCRIBE QUERY NOTIFICATIONS | DATABASE |
| TO | TAKE OWNERSHIP | ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTION |
| UP | UPDATE | DATABASE, OBJECT, SCHEMA |
| VW | VIEW DEFINITION | APPLICATION 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 |
| VWCK | VIEW ANY COLUMN ENCRYPTION KEY DEFINITION | DATABASE |
| VWCM | VIEW ANY COLUMN MASTER KEY DEFINITION | DATABASE |
| VWCT | VIEW CHANGE TRACKING | TABLE, SCHEMA |
| VWDS | VIEW DATABASE STATE | DATABASE |
Permissions
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 was granted some permission. For more information, see Metadata Visibility Configuration.
Examples
A. List all the permissions of database principals
The following query lists the permissions explicitly granted or denied to database principals.
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
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id;
B. List 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
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.objects AS o ON pe.major_id = o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id;
C. List permissions for a specific object
You can use the previous example to query permissions specific to a single database object.
For example, consider the following granular permissions granted to a database user test in the sample database AdventureWorksDW2022:
GRANT SELECT ON dbo.vAssocSeqOrders TO [test];
Find the granular permissions assigned to dbo.vAssocSeqOrders:
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
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.objects AS o ON pe.major_id = o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE o.name = 'vAssocSeqOrders'
AND s.name = 'dbo';
Returns the output:
principal_id name type_desc authentication_type_desc state_desc permission_name ObjectName
5 test SQL_USER INSTANCE GRANT SELECT dbo.vAssocSeqOrders
See also
- Securables
- Permissions Hierarchy (Database Engine)
- Security Catalog Views (Transact-SQL)
- Catalog Views (Transact-SQL)