sys-database-permissions-transact-sql.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	ms.custom	f1_keywords	helpviewer_keywords	dev_langs	monikerRange
sys.database_permissions (Transact-SQL)	sys.database_permissions returns a row for every permission or column-exception permission in the database.	VanMSFT	vanto	06/16/2023	sql	system-objects	reference	ignite-2024	database_permissions sys.database_permissions_TSQL database_permissions_TSQL sys.database_permissions	sys.database_permissions catalog view	TSQL	>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

sys.database_permissions (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

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: [!INCLUDEsql2008-md] and later versions. 6 = Type 10 = XML Schema Collection - Applies to: [!INCLUDEsql2008-md] and later versions. 15 = Message Type - Applies to: [!INCLUDEsql2008-md] and later versions. 16 = Service Contract - Applies to: [!INCLUDEsql2008-md] and later versions. 17 = Service - Applies to: [!INCLUDEsql2008-md] and later versions. 18 = Remote Service Binding - Applies to: [!INCLUDEsql2008-md] and later versions. 19 = Route - Applies to: [!INCLUDEsql2008-md] and later versions. 23 =Full-Text Catalog - Applies to: [!INCLUDEsql2008-md] and later versions. 24 = Symmetric Key - Applies to: [!INCLUDEsql2008-md] and later versions. 25 = Certificate - Applies to: [!INCLUDEsql2008-md] and later versions. 26 = Asymmetric Key - Applies to: [!INCLUDEsql2008-md] and later versions. 29 = Fulltext Stoplist - Applies to: [!INCLUDEsql2008-md] and later versions. 31 = Search Property List - Applies to: [!INCLUDEsql2008-md] and later versions. 32 = Database Scoped Credential - Applies to: [!INCLUDEsssql16-md] and later versions. 34 = External Language - Applies to: [!INCLUDEsssql19-md] 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: [!INCLUDEssSQL11] 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: [!INCLUDEsql2008-md] 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

REVOKE and column-exception permissions

In most cases, the REVOKE command will remove the GRANT or DENY entry from sys.database_permissions.

However, it is possible to GRANT or DENY permissions on a object and then REVOKE that permission on a column. This column-exception permission will show up as REVOKE in sys.database_permissions. Consider the following example:

GRANTSELECTONPerson.Person TO [Sales]; REVOKESELECTONPerson.Person(AdditionalContactInfo) FROM [Sales];

These permissions will show up in sys.database_permissions as one GRANT (on the table) and one REVOKE (on the column).

Important

REVOKE is different from DENY, as the Sales principal may still have access to the column through other permissions. Had we denied permissions rather than revoking them, Sales would not be able to view the contents of the column because DENY always supersedes GRANT.

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).

[!INCLUDEssCatViewPerm] 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.

SELECTpr.principal_id ,pr.name ,pr.type_desc ,pr.authentication_type_desc ,pe.state_desc ,pe.permission_nameFROMsys.database_principalsAS pr INNER JOINsys.database_permissionsAS pe ONpe.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.

SELECTpr.principal_id ,pr.name ,pr.type_desc ,pr.authentication_type_desc ,pe.state_desc ,pe.permission_name ,s.name+'.'+o.nameAS ObjectName FROMsys.database_principalsAS pr INNER JOINsys.database_permissionsAS pe ONpe.grantee_principal_id=pr.principal_idINNER JOINsys.objectsAS o ONpe.major_id=o.object_idINNER JOINsys.schemasAS s ONo.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 [!INCLUDE sssampledbdwobject-md]:

GRANTSELECTONdbo.vAssocSeqOrders TO [test];

Find the granular permissions assigned to dbo.vAssocSeqOrders:

SELECTpr.principal_id ,pr.name ,pr.type_desc ,pr.authentication_type_desc ,pe.state_desc ,pe.permission_name ,s.name+'.'+o.nameAS ObjectName FROMsys.database_principalsAS pr INNER JOINsys.database_permissionsAS pe ONpe.grantee_principal_id=pr.principal_idINNER JOINsys.objectsAS o ONpe.major_id=o.object_idINNER JOINsys.schemasAS s ONo.schema_id=s.schema_idWHEREo.name='vAssocSeqOrders'ANDs.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)

Next steps

• Grant a Permission to a Principal
• GRANT (Transact-SQL)