sys.server_principals (Transact-SQL)sys.server_principals (Transact-SQL)

本主題適用於:是SQL Server (從 2008 開始)否Azure SQL Database否Azure SQL 資料倉儲 是平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse 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 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
sys.indexesis_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 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 fixed server roles. 如需詳細資訊,請參閱 伺服器層級角色For more information, see Server-Level Roles.

PermissionsPermissions

任何登入都可以查看他們自己的登入名稱、系統登入和固定伺服器角色。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 Configuration For 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)
目錄檢視 (Transact-SQL) Catalog Views (Transact-SQL)
主體 (Database Engine) Principals (Database Engine)
權限階層 (Database Engine)Permissions Hierarchy (Database Engine)