| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.server_principals (Transact-SQL) |
sys.server_principals (Transact-SQL) |
VanMSFT |
vanto |
08/11/2023 |
sql |
system-objects |
reference |
|
|
|
>=aps-pdw-2016||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-current||=azuresqldb-mi-current |
[!INCLUDE sql-asdb-asdbmi-pdw]
Contains a row for every server-level principal.
[!INCLUDE entra-id]
| 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. |
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
[!INCLUDEssCatViewPerm] For more information, see Metadata Visibility Configuration.
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.
Security Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
Principals (Database Engine)
Permissions Hierarchy (Database Engine)