sys.server_permissions (Transact-SQL)sys.server_permissions (Transact-SQL)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

为每个服务器级权限返回一行。Returns one row for each server-level permission.

列名称Column name 数据类型Data type 说明Description
班级class tinyinttinyint 标识存在权限的对象的类。Identifies class of thing on which permission exists.

100 = 服务器100 = Server

101 = 服务器主体101 = Server-principal

105 = 端点105 = Endpoint
class_descclass_desc nvarchar (60)nvarchar(60) 权限所针对的类的说明。Description of class on which permission exists. 以下值之一:One of the following values:

服务SERVER

SERVER_PRINCIPALSERVER_PRINCIPAL

ENDPOINTENDPOINT
major_idmajor_id intint 存在权限的安全对象的 ID,根据类解释。ID of the securable on which permission exists, interpreted according to class. 通常情况下,这只是一种应用于类表示的内容的 ID。For most, this is just the kind of ID that applies to what the class represents. 非标准的解释如下:Interpretation for non-standard is as follows:

100 = 始终0100 = Always 0
minor_idminor_id intint 存在权限的对象的辅助 ID,根据类进行解释。Secondary ID of thing on which permission exists, interpreted according to class.
grantee_principal_idgrantee_principal_id intint 向其授予权限的服务器主体 ID。Server-principal-ID to which the permissions are granted.
grantor_principal_idgrantor_principal_id intint 这些权限的授权者的服务器主体 ID。Server-principal-ID of the grantor of these permissions.
type type char (4)char(4) 服务器权限类型。Server permission type. 有关权限类型的列表,请参阅下一个表。For a list of permission types, see the next table.
permission_namepermission_name nvarchar(128)nvarchar(128) 权限名称。Permission name.
状态state char (1)char(1) 权限状态:Permission state:

D = 拒绝D = Deny

R = 撤消R = Revoke

G = 授予G = Grant

W = Grant With Grant 选项W = Grant With Grant option
state_descstate_desc nvarchar (60)nvarchar(60) 权限状态的说明:Description of permission state:

DENYDENY

REVOKEREVOKE

GRANTGRANT

GRANT_WITH_GRANT_OPTIONGRANT_WITH_GRANT_OPTION
权限类型Permission type 权限名称Permission name 适用于安全对象Applies to securable
AAESAAES ALTER ANY EVENT SESSIONALTER ANY EVENT SESSION SERVERSERVER
ADBOADBO ADMINISTER BULK OPERATIONSADMINISTER BULK OPERATIONS SERVERSERVER
ALAL ALTERALTER ENDPOINT、LOGINENDPOINT, LOGIN
ALAAALAA ALTER ANY SERVER AUDITALTER ANY SERVER AUDIT SERVERSERVER
ALAGALAG ALTER ANY AVAILABILITY GROUPALTER ANY AVAILABILITY GROUP SERVERSERVER
ALCDALCD ALTER ANY CREDENTIALALTER ANY CREDENTIAL SERVERSERVER
ALCOALCO ALTER ANY CONNECTIONALTER ANY CONNECTION SERVERSERVER
ALDBALDB ALTER ANY DATABASEALTER ANY DATABASE SERVERSERVER
ALESALES ALTER ANY EVENT NOTIFICATIONALTER ANY EVENT NOTIFICATION SERVERSERVER
ALHEALHE ALTER ANY ENDPOINTALTER ANY ENDPOINT SERVERSERVER
ALLGALLG ALTER ANY LOGINALTER ANY LOGIN SERVERSERVER
ALLSALLS ALTER ANY LINKED SERVERALTER ANY LINKED SERVER SERVERSERVER
ALRSALRS ALTER RESOURCESALTER RESOURCES SERVERSERVER
ALSRALSR ALTER ANY SERVER ROLEALTER ANY SERVER ROLE SERVERSERVER
ALSSALSS ALTER SERVER STATEALTER SERVER STATE SERVERSERVER
ALSTALST ALTER SETTINGSALTER SETTINGS SERVERSERVER
ALTRALTR ALTER TRACEALTER TRACE SERVERSERVER
AUTHAUTH AUTHENTICATE SERVERAUTHENTICATE SERVER SERVERSERVER
CADBCADB CONNECT ANY DATABASECONNECT ANY DATABASE SERVERSERVER
CLCL CONTROLCONTROL ENDPOINT、LOGINENDPOINT, LOGIN
CLCL CONTROL SERVERCONTROL SERVER SERVERSERVER
COCO CONNECTCONNECT ENDPOINTENDPOINT
COSQCOSQ CONNECT SQLCONNECT SQL SERVERSERVER
CRACCRAC CREATE AVAILABILITY GROUPCREATE AVAILABILITY GROUP SERVERSERVER
CRDBCRDB CREATE ANY DATABASECREATE ANY DATABASE SERVERSERVER
CRDECRDE CREATE DDL EVENT NOTIFICATIONCREATE DDL EVENT NOTIFICATION SERVERSERVER
CRHECRHE CREATE ENDPOINTCREATE ENDPOINT SERVERSERVER
CRSRCRSR CREATE SERVER ROLECREATE SERVER ROLE SERVERSERVER
CRTECRTE CREATE TRACE EVENT NOTIFICATIONCREATE TRACE EVENT NOTIFICATION SERVERSERVER
IALIAL IMPERSONATE ANY LOGINIMPERSONATE ANY LOGIN SERVERSERVER
IMIM IMPERSONATEIMPERSONATE LOGINLOGIN
SHDNSHDN 关机SHUTDOWN SERVERSERVER
SUSSUS SELECT ALL USER SECURABLESSELECT ALL USER SECURABLES SERVERSERVER
TOTO TAKE OWNERSHIPTAKE OWNERSHIP ENDPOINTENDPOINT
VWVW VIEW DEFINITIONVIEW DEFINITION ENDPOINT、LOGINENDPOINT, LOGIN
VWADVWAD VIEW ANY DEFINITIONVIEW ANY DEFINITION SERVERSERVER
VWDBVWDB VIEW ANY DATABASEVIEW ANY DATABASE SERVERSERVER
VWSSVWSS VIEW SERVER STATEVIEW SERVER STATE SERVERSERVER
XAXA EXTERNAL ACCESSEXTERNAL ACCESS SERVERSERVER
XUXU UNSAFE ASSEMBLYUNSAFE ASSEMBLY SERVERSERVER

权限Permissions

任何用户都可以查看自己的权限。Any user can see their own permissions. 要查看其他登录权限,需要获取 VIEW DEFINITION、ALTER ANY LOGIN 或任何相关的登录权限。To see permissions for other logins, requires VIEW DEFINITION, ALTER ANY LOGIN, or any permission on a login. 要查看用户定义的服务器角色,需要获取 ALTER ANY SERVER ROLE 或相关的角色成员身份。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. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

示例Examples

以下查询将列出明确对服务器主体授予或拒绝的权限。The following query lists the permissions explicitly granted or denied to server principals.

重要

固定服务器角色的权限不会出现在 sys.server_permissions 中。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

Transact-sql)(安全目录视图 Security Catalog Views (Transact-SQL)
安全对象 Securables
目录视图 (Transact-SQL) Catalog Views (Transact-SQL)
权限 (数据库引擎) Permissions (Database Engine)
权限层次结构(数据库引擎)Permissions Hierarchy (Database Engine)