sys.database_permissions (Transact-SQL)

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.

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.

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_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
major_id int ID 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_id int Secondary-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_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 through SQL Server 2016.

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 through SQL Server 2016.

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 has been granted some permission. For more information, see Metadata Visibility Configuration.

Examples

A: Listing 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  
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;  

See Also

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