sys.server_permissions (Transact-SQL)

 

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

Returns one row for each server-level permission.

Column nameData typeDescription
classtinyintIdentifies class of thing on which permission exists.

100 = Server

101 = Server-principal

105 = Endpoint
class_descnvarchar(60)Description of class on which permission exists. One of the following values:

 SERVER

 SERVER_PRINCIPAL

 ENDPOINT
major_idintID of the securable on which permission exists, interpreted according to class. For most, this is just the kind of ID that applies to what the class represents. Interpretation for non-standard is as follows:

100 = Always 0
minor_idintSecondary ID of thing on which permission exists, interpreted according to class.
grantee_principal_idintServer-principal-ID to which the permissions are granted.
grantor_principal_idintServer-principal-ID of the grantor of these permissions.
typechar(4)Server permission type. For a list of permission types, see the next table.
permission_namenvarchar(128)Permission name.
statechar(1)Permission state:

D = Deny

R = Revoke

G = Grant

W = Grant With Grant option
state_descnvarchar(60)Description of permission state:

DENY

REVOKE

GRANT

GRANT_WITH_GRANT_OPTION
Permission typePermission nameApplies to securable
ADBOADMINISTER BULK OPERATIONSSERVER
ALALTERENDPOINT, LOGIN
ALCDALTER ANY CREDENTIALSERVER
ALCOALTER ANY CONNECTIONSERVER
ALDBALTER ANY DATABASESERVER
ALESALTER ANY EVENT NOTIFICATIONSERVER
ALHEALTER ANY ENDPOINTSERVER
ALLGALTER ANY LOGINSERVER
ALLSALTER ANY LINKED SERVERSERVER
ALRSALTER RESOURCESSERVER
ALSSALTER SERVER STATESERVER
ALSTALTER SETTINGSSERVER
ALTRALTER TRACESERVER
AUTHAUTHENTICATE SERVERSERVER
CLCONTROLENDPOINT, LOGIN
CLCONTROL SERVERSERVER
COCONNECTENDPOINT
COSQCONNECT SQLSERVER
CRDBCREATE ANY DATABASESERVER
CRDECREATE DDL EVENT NOTIFICATIONSERVER
CRHECREATE ENDPOINTSERVER
CRTECREATE TRACE EVENT NOTIFICATIONSERVER
IMIMPERSONATELOGIN
SHDNSHUTDOWNSERVER
TOTAKE OWNERSHIPENDPOINT
VWVIEW DEFINITIONENDPOINT, LOGIN
VWADVIEW ANY DEFINITIONSERVER
VWDBVIEW ANY DATABASESERVER
VWSSVIEW SERVER STATESERVER
XAEXTERNAL ACCESSSERVER

Any user can see their own permissions. To see permissions for other logins, requires VIEW DEFINITION, ALTER ANY LOGIN, or any permission on a login. To see user-defined server roles, requires ALTER ANY SERVER ROLE, or membership in the role.

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

The following query lists the permissions explicitly granted or denied to server principals.

System_CAPS_ICON_important.jpg Important


The permissions of fixed server roles do not appear in sys.server_permissions. Therefore, server principals may have additional permissions not listed here.

SELECT pr.principal_id, pr.name, pr.type_desc,   
    pe.state_desc, pe.permission_name   
FROM sys.server_principals AS pr   
JOIN sys.server_permissions AS pe   
    ON pe.grantee_principal_id = pr.principal_id;  

Security Catalog Views (Transact-SQL)
Securables
Catalog Views (Transact-SQL)
Permissions (Database Engine)
Permissions Hierarchy (Database Engine)

Community Additions

ADD
Show: