sys.server_permissions (Transact-SQL)
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.
|
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
Reference
Security Catalog Views (Transact-SQL)