sys.database_permissions (Transact-SQL)
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 6 = Type 10 = XML Schema Collection 15 = Message Type 16 = Service Contract 17 = Service 18 = Remote Service Binding 19 = Route 23 = Full-Text Catalog 24 = Symmetric Key 25 = Certificate 26 = Asymmetric Key |
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_KEY 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, 8 = 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 |
| Permission type | Permission name | Applies to securable |
|---|---|---|
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 |
CRSV |
CREATE SERVICE |
DATABASE |
CRTB |
CREATE TABLE |
DATABASE |
CRTY |
CREATE TYPE |
DATABASE |
CRVW |
CREATE VIEW |
DATABASE |
CRXS |
CREATE XML SCHEMA COLLECTION |
DATABASE |
DL |
DELETE |
DATABASE, OBJECT, SCHEMA |
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 |
VWDS |
VIEW DATABASE STATE |
DATABASE |
See Also
Reference
Security Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
Other Resources
Securables
Permissions Hierarchy