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 name Data type Description
class tinyint Identifies class of thing on which permission exists.

100 = Server

101 = Server-principal

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

SERVER

SERVER_PRINCIPAL

ENDPOINT
major_id int ID 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_id int Secondary ID of thing on which permission exists, interpreted according to class.
grantee_principal_id int Server-principal-ID to which the permissions are granted.
grantor_principal_id int Server-principal-ID of the grantor of these permissions.
type char(4) Server 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
ADBO ADMINISTER BULK OPERATIONS SERVER
AL ALTER ENDPOINT, LOGIN
ALCD ALTER ANY CREDENTIAL SERVER
ALCO ALTER ANY CONNECTION SERVER
ALDB ALTER ANY DATABASE SERVER
ALES ALTER ANY EVENT NOTIFICATION SERVER
ALHE ALTER ANY ENDPOINT SERVER
ALLG ALTER ANY LOGIN SERVER
ALLS ALTER ANY LINKED SERVER SERVER
ALRS ALTER RESOURCES SERVER
ALSS ALTER SERVER STATE SERVER
ALST ALTER SETTINGS SERVER
ALTR ALTER TRACE SERVER
AUTH AUTHENTICATE SERVER SERVER
CL CONTROL ENDPOINT, LOGIN
CL CONTROL SERVER SERVER
CO CONNECT ENDPOINT
COSQ CONNECT SQL SERVER
CRDB CREATE ANY DATABASE SERVER
CRDE CREATE DDL EVENT NOTIFICATION SERVER
CRHE CREATE ENDPOINT SERVER
CRTE CREATE TRACE EVENT NOTIFICATION SERVER
IM IMPERSONATE LOGIN
SHDN SHUTDOWN SERVER
TO TAKE OWNERSHIP ENDPOINT
VW VIEW DEFINITION ENDPOINT, LOGIN
VWAD VIEW ANY DEFINITION SERVER
VWDB VIEW ANY DATABASE SERVER
VWSS VIEW SERVER STATE SERVER
XA EXTERNAL ACCESS SERVER

Permissions

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.

Examples

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

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;  

See Also

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