sys.server_principals (Transact-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 nameData typeDescription
namesysnameName of the principal. Is unique within a server.
principal_idintID number of the Principal. Is unique within a server.
sidvarbinary(85)SID (Security-IDentifier) of the principal. If Windows principal, then it matches Windows SID.
typechar(1)Principal type:

S = SQL login

U = Windows login

G = Windows group

R = Server role

C = Login mapped to a certificate

K = Login mapped to an asymmetric key
type_descnvarchar(60)Description of the principal type:






is_disabledint1 = Login is disabled.
create_datedatetimeTime at which the principal was created.
modify_datedatetimeTime at which the principal definition was last modified.
default_database_namesysnameDefault database for this principal.
default_language_namesysnameDefault language for this principal.
credential_idintID of a credential associated with this principal. If no credential is associated with this principal, credential_id will be NULL.
owning_principal_idintThe principal_id of the owner of a server role. NULL if the principal is not a server role.
is_fixed_rolebitReturns 1 if the principal is one of the fixed server roles. For more information, see Server-Level Roles.

Any login can see their own login name, the system logins, and the fixed server roles. To see other logins, requires ALTER ANY LOGIN, or a permission on the 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.

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

System_CAPS_ICON_important.jpg 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.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;  

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

Community Additions