sys.fn_builtin_permissions (Transact-SQL)

 

Updated: November 30, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure 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 and Azure SQL Database, and it returns all permissions regardless of whether they are supported on the current platform. Most permissions apply to all platforms, but some do not. For example server level permissions cannot be granted on SQL Database. 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 | DATABASE SCOPED CREDENTIAL    
    | 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
ALALTER
 Applies t o: SQL Server vNext CTP 1.0 and SQL Database.
DATABASE SCOPED CREDENTIAL
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
CLCONTROL
 Applies to: SQL Server vNext CTP 1.0 and SQL Database.
DATABASE SCOPED CREDENTIAL
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
DABOADMINISTER DATABASE BULK OPERATIONS
 Applies to: SQL Database.
DATABASE
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
RFREFERENCES
 Applies to: SQL Server vNext CTP 1.0 and SQL Database.
DATABASE SCOPED CREDENTIAL
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 OWNERSHIP
 Applies to: SQL Server vNext CTP 1.0 and SQL Database.
DATABASE SCOPED CREDENTIAL
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 DEFINITION
 Applies to: SQL Server vNext CTP 1.0 and SQL Database.
DATABASE SCOPED CREDENTIAL
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.

The following graphic shows the permissions and their relationships to each other. Some of the higher level permissions (such as CONTROL SERVER) are listed many times.

Database Engine Permissions

System_CAPS_ICON_note.jpg Note

As part of this topic the poster is far to small to read. Download the Database Engine Permissions Poster from http://go.microsoft.com/fwlink/?LinkId=229142.

Requires membership in the public role.

A. Listing all built in permissions

Use DEFAULT or an empty string to return all permissions.

SELECT * FROM sys.fn_builtin_permissions(DEFAULT);
SELECT * FROM sys.fn_builtin_permissions('');  

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

Specify a class to return all possible permissions for that class.

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: