sys.server_principals (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Analytics Platform System (PDW)
Contains a row for every server-level principal.
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
Column name | Data type | Description |
---|---|---|
name | sysname | Name of the principal. Is unique within a server. |
principal_id | int | ID number of the Principal. Is unique within a server. |
sid | varbinary(85) | SID (Security-IDentifier) of the principal. If Windows principal, then it matches Windows SID. |
type | char(1) | Principal type: S = SQL login U = Windows login G = Windows group R = Server role C = Login mapped to a certificate E = External Login from Microsoft Entra ID X = External group from Microsoft Entra group or applications K = Login mapped to an asymmetric key |
type_desc | nvarchar(60) | Description of the principal type: SQL_LOGIN WINDOWS_LOGIN WINDOWS_GROUP SERVER_ROLE CERTIFICATE_MAPPED_LOGIN EXTERNAL_LOGIN EXTERNAL_GROUP ASYMMETRIC_KEY_MAPPED_LOGIN |
is_disabled | int | 1 = Login is disabled. |
create_date | datetime | Time at which the principal was created. |
modify_date | datetime | Time at which the principal definition was last modified. |
default_database_name | sysname | Default database for this principal. |
default_language_name | sysname | Default language for this principal. |
credential_id | int | ID of a credential associated with this principal. If no credential is associated with this principal, credential_id will be NULL. |
owning_principal_id | int | The principal_id of the owner of a server role. NULL if the principal is not a server role. |
is_fixed_role | bit | 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. 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.
In Azure SQL Database, only the following principals can see all logins:
- members of the server role ##MS_LoginManager## or special database role loginmanager in
master
- the Microsoft Entra admin and SQL server admin
The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was 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 (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;
Note
The permissions of fixed server roles do not appear in sys.server_permissions.
See also
Security Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
Principals (Database Engine)
Permissions Hierarchy (Database Engine)