sys.server_principals (Transact-SQL)sys.server_principals (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

每个服务器级别主体占一行。Contains a row for every server-level principal.

列名Column name 数据类型Data type 描述Description
namename sysnamesysname 主体的名称。Name of the principal. 是在服务器中唯一的。Is unique within a server.
principal_idprincipal_id intint 主体的 ID 号。ID number of the Principal. 是在服务器中唯一的。Is unique within a server.
sidsid varbinary(85)varbinary(85) 主体的 SID(安全标识符)。SID (Security-IDentifier) of the principal. 如果是 Windows 主体,则它与 Windows SID 匹配。If Windows principal, then it matches Windows SID.
typetype char(1)char(1) 主体类型:Principal type:

S = SQL 登录名S = SQL login

U = Windows 登录名U = Windows login

G = Windows 组G = Windows group

R = 服务器角色R = Server role

C = 映射到证书的登录名C = Login mapped to a certificate

K = 映射到非对称密钥的登录名K = Login mapped to an asymmetric key
type_desctype_desc nvarchar(60)nvarchar(60) 主体类型的说明:Description of the principal type:

SQL_LOGINSQL_LOGIN

WINDOWS_LOGINWINDOWS_LOGIN

WINDOWS_GROUPWINDOWS_GROUP

SERVER_ROLESERVER_ROLE

CERTIFICATE_MAPPED_LOGINCERTIFICATE_MAPPED_LOGIN

ASYMMETRIC_KEY_MAPPED_LOGINASYMMETRIC_KEY_MAPPED_LOGIN
is_disabledis_disabled intint 1 = 禁用登录名。1 = Login is disabled.
create_datecreate_date datetimedatetime 主体的创建时间。Time at which the principal was created.
modify_datemodify_date datetimedatetime 上次修改主体定义的时间。Time at which the principal definition was last modified.
default_database_namedefault_database_name sysnamesysname 该主体的默认数据库。Default database for this principal.
default_language_namedefault_language_name sysnamesysname 该主体的默认语言。Default language for this principal.
credential_idcredential_id intint 与该主体关联的凭据的 ID。ID of a credential associated with this principal. 如果没有与该主体关联的凭据,则 credential_id 将为 NULL。If no credential is associated with this principal, credential_id will be NULL.
owning_principal_idowning_principal_id intint Principal_id的服务器角色的所有者。The principal_id of the owner of a server role. 如果主体不是服务器角色,则为 NULL。NULL if the principal is not a server role.
is_fixed_roleis_fixed_role bitbit 如果主体是具有固定权限的内置服务器角色之一,则返回 1。Returns 1 if the principal is one of the built-in server roles with fixed permissions. 有关详细信息,请参阅 服务器级别角色For more information, see Server-Level Roles.

权限Permissions

任何登录都可以查看自己的登录名称、系统登录和固定的数据库角色。Any login can see their own login name, the system logins, and the fixed server roles. 要查看其他登录,需要获取 ALTER ANY LOGIN 或有关登录的权限。To see other logins, requires ALTER ANY LOGIN, or a permission on the 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 (other than public) 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)
目录视图 (Transact-SQL) Catalog Views (Transact-SQL)
主体(数据库引擎) Principals (Database Engine)
权限层次结构(数据库引擎)Permissions Hierarchy (Database Engine)