Edit

Share via

sys.server_principals (Transact-SQL)

  • Article

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) Security Identifier (SID) of the principal.
A Windows principal's SID matches their Windows SID.
A Microsoft Entra user identity's SID is the binary representation of its Microsoft Entra object ID.
A Microsoft Entra group's SID is the binary representation of its Microsoft Entra object ID.
A Microsoft Entra application's SID is the binary representation of its Microsoft Entra client ID.
type char(1) Principal type:

S = SQL login
R = Server role

Available in SQL Server, Azure SQL Managed Instance, and PDW (In preview in Azure SQL Database):
E = External login or application from Microsoft Entra ID
X = External group from Microsoft Entra ID

Available in SQL Server, Azure SQL Managed Instance, and PDW (not Azure SQL Database):
U = Windows login
G = Windows group
C = Login mapped to a certificate
K = Login mapped to an asymmetric key
type_desc nvarchar(60) Description of the principal type:

SQL_LOGIN
SERVER_ROLE

Available in SQL Server, Azure SQL Managed Instance, and PDW (In preview in Azure SQL Database):
EXTERNAL_LOGIN
EXTERNAL_GROUP

Available in SQL Server, Azure SQL Managed Instance, and PDW (not Azure SQL Database):
WINDOWS_LOGIN
WINDOWS_GROUP
CERTIFICATE_MAPPED_LOGIN
ASYMMETRIC_KEY_MAPPED_LOGIN
is_disabled int 1 = Login is disabled.
0 = Login is enabled.
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 the principal.
default_language_name sysname Default language for the principal.
credential_id int ID of a credential associated with the principal. If no credential is associated with this principal, credential_id is 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. Viewing other logins requires ALTER ANY LOGIN, or a permission on the login. Viewing 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.

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;

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.

Related content

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