sys.fn_builtin_permissions (Transact-SQL)

 

Updated: April 20, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns a description of the built in permissions hierarchy of the server. sys.fn_builtin_permissions can only be called on SQL Server, however many of the permissions listed below are available on Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse. For information about which platforms support each permission, see Permissions (Database Engine).

Topic link icon Transact-SQL Syntax Conventions

  
sys.fn_builtin_permissions ( [ DEFAULT | NULL ]  
    | empty_string | '<securable_class>' } )  
  
<securable_class> ::=   
      APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY | AVAILABILITY GROUP  
    | CERTIFICATE | CONTRACT | DATABASE | ENDPOINT | FULLTEXT CATALOG   
    | FULLTEXT STOPLIST | LOGIN | MESSAGE TYPE | OBJECT   
    | REMOTE SERVICE BINDING | ROLE | ROUTE | SCHEMA | SEARCH PROPERTY LIST   
    | SERVER | SERVER ROLE | SERVICE | SYMMETRIC KEY | TYPE | USER   
    | XML SCHEMA COLLECTION  

DEFAULT
When it is called with the DEFAULT option (without quotes), the function will return a complete list of built in permissions.

NULL
Equivalent to DEFAULT.

empty_string
Equivalent to DEFAULT.

'<securable_class>'
When it is called with the name of one securable class, sys.fn_builtin_permissions will return all permissions that apply to the class. <securable_class> is a string literal that requires quotation marks. nvarchar(60)

Column nameData typeCollationDescription
class_descnvarchar(60)Collation of the serverDescription of the securable class.
permission_namenvarchar(60)Collation of the serverPermission name.
typevarchar(4)Collation of the serverCompact permission type code. See the table that follows.
covering_permission_namenvarchar(60)Collation of the serverIf not NULL, this is the name of the permission on this class that implies the other permissions on this class.
parent_class_descnvarchar(60)Collation of the serverIf not NULL, this is the name of the parent class that contains the current class.
parent_covering_permission_namenvarchar(60)Collation of the serverIf not NULL, this is the name of the permission on the parent class that implies all other permissions on that class.

Permission Types

Permission typePermission nameApplies to securable or class
AADSALTER ANY DATABASE EVENT SESSION

 Applies to: SQL Server (SQL Server 2014 through current version).
DATABASE
AAESALTER ANY EVENT SESSIONSERVER
AAMKALTER ANY MASK

 Applies to: SQL Server (SQL Server 2016 through current version).
DATABASE
ADBOADMINISTER BULK OPERATIONSSERVER
AEDSALTER ANY EXTERNAL DATA SOURCE

 Applies to: SQL Server (SQL Server 2016 through current version).
DATABASE
AEFFALTER ANY EXTERNAL FILE FORMAT

 Applies to: SQL Server (SQL Server 2016 through current version).
DATABASE
ALALTERAPPLICATION ROLE
ALALTERASSEMBLY
ALALTER

 Applies to: SQL Server (SQL Server 2012 through current version).
AVAILABILITY GROUP
ALALTERASYMMETRIC KEY
ALALTERCERTIFICATE
ALALTERCONTRACT
ALALTERDATABASE
ALALTERENDPOINT
ALALTERFULLTEXT CATALOG
ALALTERFULLTEXT STOPLIST
ALALTERLOGIN
ALALTERMESSAGE TYPE
ALALTEROBJECT
ALALTERREMOTE SERVICE BINDING
ALALTERROLE
ALALTERROUTE
ALALTERSCHEMA
ALALTERSEARCH PROPERTY LIST
ALALTER

 Applies to: SQL Server (SQL Server 2012 through current version).
SERVER ROLE
ALALTERSERVICE
ALALTERSYMMETRIC KEY
ALALTERUSER
ALALTERXML SCHEMA COLLECTION
ALAAALTER ANY SERVER AUDITSERVER
ALAGALTER ANY AVAILABILITY GROUP

 Applies to: SQL Server (SQL Server 2012 through current version).
SERVER
ALAKALTER ANY ASYMMETRIC KEYDATABASE
ALARALTER ANY APPLICATION ROLEDATABASE
ALASALTER ANY ASSEMBLYDATABASE
ALCDALTER ANY CREDENTIALSERVER
ALCFALTER ANY CERTIFICATEDATABASE
ALCKALTER ANY COLUMN ENCRYPTION KEY

 Applies to: SQL Server (SQL Server 2016 through current version).
DATABASE
ALCMALTER ANY COLUMN MASTER KEY

 Applies to: SQL Server (SQL Server 2016 through current version).
DATABASE
ALCOALTER ANY CONNECTIONSERVER
ALDAALTER ANY DATABASE AUDITDATABASE
ALDBALTER ANY DATABASESERVER
ALDCALTER ANY DATABASE SCOPED CONFIGURATION

 Applies to: SQL Server (SQL Server 2016 through current version).
DATABASE
ALDSALTER ANY DATASPACEDATABASE
ALEDALTER ANY DATABASE EVENT NOTIFICATIONDATABASE
ALESALTER ANY EVENT NOTIFICATIONSERVER
ALFTALTER ANY FULLTEXT CATALOGDATABASE
ALHEALTER ANY ENDPOINTSERVER
ALLGALTER ANY LOGINSERVER
ALLSALTER ANY LINKED SERVERSERVER
ALMTALTER ANY MESSAGE TYPEDATABASE
ALRLALTER ANY ROLEDATABASE
ALRSALTER RESOURCESSERVER
ALRTALTER ANY ROUTEDATABASE
ALSBALTER ANY REMOTE SERVICE BINDINGDATABASE
ALSCALTER ANY CONTRACTDATABASE
ALSKALTER ANY SYMMETRIC KEYDATABASE
ALSMALTER ANY SCHEMADATABASE
ALSPALTER ANY SECURITY POLICY

 Applies to: SQL Server (SQL Server 2016 through current version).
DATABASE
ALSRALTER ANY SERVER ROLE

 Applies to: SQL Server (SQL Server 2012 through current version).
SERVER
ALSSALTER SERVER STATESERVER
ALSTALTER SETTINGSSERVER
ALSVALTER ANY SERVICEDATABASE
ALTGALTER ANY DATABASE DDL TRIGGERDATABASE
ALTRALTER TRACESERVER
ALUSALTER ANY USERDATABASE
AUTHAUTHENTICATEDATABASE
AUTHAUTHENTICATE SERVERSERVER
BADBBACKUP DATABASEDATABASE
BALOBACKUP LOGDATABASE
CADBCONNECT ANY DATABASE

 Applies to: SQL Server (SQL Server 2014 through current version).
SERVER
CLCONTROLAPPLICATION ROLE
CLCONTROLASSEMBLY
CLCONTROLASYMMETRIC KEY
CLCONTROL

 Applies to: SQL Server (SQL Server 2012 through current version).
AVAILABILITY GROUP
CLCONTROLCERTIFICATE
CLCONTROLCONTRACT
CLCONTROLDATABASE
CLCONTROLENDPOINT
CLCONTROLFULLTEXT CATALOG
CLCONTROLFULLTEXT STOPLIST
CLCONTROLLOGIN
CLCONTROLMESSAGE TYPE
CLCONTROLOBJECT
CLCONTROLREMOTE SERVICE BINDING
CLCONTROLROLE
CLCONTROLROUTE
CLCONTROLSCHEMA
CLCONTROLSEARCH PROPERTY LIST
CLCONTROL SERVERSERVER
CLCONTROL

 Applies to: SQL Server (SQL Server 2012 through current version).
SERVER ROLE
CLCONTROLSERVICE
CLCONTROLSYMMETRIC KEY
CLCONTROLTYPE
CLCONTROLUSER
CLCONTROLXML SCHEMA COLLECTION
COCONNECTDATABASE
COCONNECTENDPOINT
CORPCONNECT REPLICATIONDATABASE
COSQCONNECT SQLSERVER
CPCHECKPOINTDATABASE
CRACCREATE AVAILABILITY GROUP

 Applies to: SQL Server (SQL Server 2012 through current version).
SERVER
CRAGCREATE AGGREGATEDATABASE
CRAKCREATE ASYMMETRIC KEYDATABASE
CRASCREATE ASSEMBLYDATABASE
CRCFCREATE CERTIFICATEDATABASE
CRDBCREATE ANY DATABASESERVER
CRDBCREATE DATABASEDATABASE
CRDECREATE DDL EVENT NOTIFICATIONSERVER
CRDFCREATE DEFAULTDATABASE
CREDCREATE DATABASE DDL EVENT NOTIFICATIONDATABASE
CRFNCREATE FUNCTIONDATABASE
CRFTCREATE FULLTEXT CATALOGDATABASE
CRHECREATE ENDPOINTSERVER
CRMTCREATE MESSAGE TYPEDATABASE
CRPRCREATE PROCEDUREDATABASE
CRQUCREATE QUEUEDATABASE
CRRLCREATE ROLEDATABASE
CRRTCREATE ROUTEDATABASE
CRRUCREATE RULEDATABASE
CRSBCREATE REMOTE SERVICE BINDINGDATABASE
CRSCCREATE CONTRACTDATABASE
CRSKCREATE SYMMETRIC KEYDATABASE
CRSMCREATE SCHEMADATABASE
CRSNCREATE SYNONYMDATABASE
CRSOCREATE SEQUENCESCHEMA
CRSRCREATE SERVER ROLE

 Applies to: SQL Server (SQL Server 2012 through current version).
SERVER
CRSVCREATE SERVICEDATABASE
CRTBCREATE TABLEDATABASE
CRTECREATE TRACE EVENT NOTIFICATIONSERVER
CRTYCREATE TYPEDATABASE
CRVWCREATE VIEWDATABASE
CRXSCREATE XML SCHEMA COLLECTIONDATABASE
DLDELETEDATABASE
DLDELETEOBJECT
DLDELETESCHEMA
EAESEXECUTE ANY EXTERNAL SCRIPT

 Applies to: SQL Server (SQL Server 2016 through current version).
DATABASE
EXEXECUTEDATABASE
EXEXECUTEOBJECT
EXEXECUTESCHEMA
EXEXECUTETYPE
EXEXECUTEXML SCHEMA COLLECTION
IALIMPERSONATE ANY LOGIN

 Applies to: SQL Server (SQL Server 2014 through current version).
SERVER
IMIMPERSONATELOGIN
IMIMPERSONATEUSER
ININSERTDATABASE
ININSERTOBJECT
ININSERTSCHEMA
KIDCKILL DATABASE CONNECTION

 Applies to: Azure SQL Database.
DATABASE
RCRECEIVEOBJECT
RFREFERENCESASSEMBLY
RFREFERENCESASYMMETRIC KEY
RFREFERENCESCERTIFICATE
RFREFERENCESCONTRACT
RFREFERENCESDATABASE
RFREFERENCESFULLTEXT CATALOG
RFREFERENCESFULLTEXT STOPLIST
RFREFERENCESSEARCH PROPERTY LIST
RFREFERENCESMESSAGE TYPE
RFREFERENCESOBJECT
RFREFERENCESSCHEMA
RFREFERENCESSYMMETRIC KEY
RFREFERENCESTYPE
RFREFERENCESXML SCHEMA COLLECTION
SHDNSHUTDOWNSERVER
SLSELECTDATABASE
SLSELECTOBJECT
SLSELECTSCHEMA
SNSENDSERVICE
SPLNSHOWPLANDATABASE
SUQNSUBSCRIBE QUERY NOTIFICATIONSDATABASE
SUSSELECT ALL USER SECURABLES

 Applies to: SQL Server (SQL Server 2014 through current version).
SERVER
TOTAKE OWNERSHIPASSEMBLY
TOTAKE OWNERSHIPASYMMETRIC KEY
TOTAKE OWNERSHIP

 Applies to: SQL Server (SQL Server 2012 through current version).
AVAILABILITY GROUP
TOTAKE OWNERSHIPCERTIFICATE
TOTAKE OWNERSHIPCONTRACT
TOTAKE OWNERSHIPDATABASE
TOTAKE OWNERSHIPENDPOINT
TOTAKE OWNERSHIPFULLTEXT CATALOG
TOTAKE OWNERSHIPFULLTEXT STOPLIST
TOTAKE OWNERSHIPSEARCH PROPERTY LIST
TOTAKE OWNERSHIPMESSAGE TYPE
TOTAKE OWNERSHIPOBJECT
TOTAKE OWNERSHIPREMOTE SERVICE BINDING
TOTAKE OWNERSHIPROLE
TOTAKE OWNERSHIPROUTE
TOTAKE OWNERSHIPSCHEMA
TOTAKE OWNERSHIP

 Applies to: SQL Server (SQL Server 2012 through current version).
SERVER ROLE
TOTAKE OWNERSHIPSERVICE
TOTAKE OWNERSHIPSYMMETRIC KEY
TOTAKE OWNERSHIPTYPE
TOTAKE OWNERSHIPXML SCHEMA COLLECTION
UMSKUNMASK

 Applies to: SQL Server (SQL Server 2016 through current version).
DATABASE
UPUPDATEDATABASE
UPUPDATEOBJECT
UPUPDATESCHEMA
VWVIEW DEFINITIONAPPLICATION ROLE
VWVIEW DEFINITIONASSEMBLY
VWVIEW DEFINITIONASYMMETRIC KEY
VWVIEW DEFINITION

 Applies to: SQL Server (SQL Server 2012 through current version).
AVAILABILITY GROUP
VWVIEW DEFINITIONCERTIFICATE
VWVIEW DEFINITIONCONTRACT
VWVIEW DEFINITIONDATABASE
VWVIEW DEFINITIONENDPOINT
VWVIEW DEFINITIONFULLTEXT CATALOG
VWVIEW DEFINITIONFULLTEXT STOPLIST
VWVIEW DEFINITIONLOGIN
VWVIEW DEFINITIONMESSAGE TYPE
VWVIEW DEFINITIONOBJECT
VWVIEW DEFINITIONREMOTE SERVICE BINDING
VWVIEW DEFINITIONROLE
VWVIEW DEFINITIONROUTE
VWVIEW DEFINITIONSCHEMA
VWVIEW DEFINITIONSEARCH PROPERTY LIST
VWVIEW DEFINITION

 Applies to: SQL Server (SQL Server 2012 through current version).
SERVER ROLE
VWVIEW DEFINITIONSERVICE
VWVIEW DEFINITIONSYMMETRIC KEY
VWVIEW DEFINITIONTYPE
VWVIEW DEFINITIONUSER
VWVIEW DEFINITIONXML SCHEMA COLLECTION
VWADVIEW ANY DEFINITIONSERVER
VWCKVIEW ANY COLUMN ENCRYPTION KEY DEFINITION

 Applies to: SQL Server (SQL Server 2016 through current version).
DATABASE
VWCMVIEW ANY COLUMN MASTER KEY DEFINITION

 Applies to: SQL Server (SQL Server 2016 through current version).
DATABASE
VWCTVIEW CHANGE TRACKINGOBJECT
VWCTVIEW CHANGE TRACKINGSCHEMA
VWDBVIEW ANY DATABASESERVER
VWDSVIEW DATABASE STATEDATABASE
VWSSVIEW SERVER STATESERVER
XAEXTERNAL ACCESS ASSEMBLYSERVER
XUUNSAFE ASSEMBLYSERVER

sys.fn_builtin_permissions is a table-valued function that emits a copy of the predefined permission hierarchy. This hierarchy includes covering permissions. The DEFAULT result set describes a directed, acyclic graph of the permissions hierarchy, of which the root is (class = SERVER, permission = CONTROL SERVER).

sys.fn_builtin_permissions does not accept correlated parameters.

sys.fn_builtin_permissions will return an empty set when it is called with a class name that is not valid.

Requires membership in the public role.

A. Listing all built in permissions

SELECT * FROM sys.fn_builtin_permissions(DEFAULT);  

B. Listing permissions that can be set on a symmetric key

SELECT * FROM sys.fn_builtin_permissions(N'SYMMETRIC KEY');  

C. Listing classes on which there is a SELECT permission

SELECT * FROM sys.fn_builtin_permissions(DEFAULT)   
    WHERE permission_name = 'SELECT';  

Permissions Hierarchy (Database Engine)
GRANT (Transact-SQL)
CREATE SCHEMA (Transact-SQL)
DROP SCHEMA (Transact-SQL)
Permissions (Database Engine)
sys.fn_my_permissions (Transact-SQL)
HAS_PERMS_BY_NAME (Transact-SQL)

Community Additions

ADD
Show: