Edit

Share via

sys.database_permissions (Transact-SQL)

  • Article

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

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

Next steps