sys.database_principals (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Returns a row for each security principal in a SQL Server database.
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
Column descriptions
Column name | Data type | Description |
---|---|---|
name | sysname | Name of principal, unique within the database. |
principal_id | int | ID of principal, unique within the database. |
type | char(1) | Principal type: A = Application role C = User mapped to a certificate E = External user from Microsoft Entra ID G = Windows group K = User mapped to an asymmetric key R = Database role S = SQL user U = Windows user X = External group from Microsoft Entra group or applications |
type_desc | nvarchar(60) | Description of principal type. APPLICATION_ROLE CERTIFICATE_MAPPED_USER EXTERNAL_USER WINDOWS_GROUP ASYMMETRIC_KEY_MAPPED_USER DATABASE_ROLE SQL_USER WINDOWS_USER EXTERNAL_GROUPS |
default_schema_name | sysname | Name to be used when SQL name does not specify a schema. Null for principals not of type S, U, or A. |
create_date | datetime | Time at which the principal was created. |
modify_date | datetime | Time at which the principal was last modified. |
owning_principal_id | int | ID of the principal that owns this principal. All fixed Database Roles are owned by dbo by default. |
sid | varbinary(85) | SID (Security Identifier) of the principal. NULL for SYS and INFORMATION SCHEMAS. |
is_fixed_role | bit | If 1, this row represents an entry for one of the fixed database roles: db_owner, db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_backupoperator, db_denydatareader, db_denydatawriter. |
authentication_type | int | Applies to: SQL Server 2012 (11.x) and later. Signifies authentication type. The following are the possible values and their descriptions. 0 : No authentication 1 : Instance authentication 2 : Database authentication 3 : Windows authentication 4 : Microsoft Entra authentication |
authentication_type_desc | nvarchar(60) | Applies to: SQL Server 2012 (11.x) and later. Description of the authentication type. The following are the possible values and their descriptions. NONE : No authenticationINSTANCE : Instance authenticationDATABASE : Database authenticationWINDOWS : Windows authenticationEXTERNAL : Microsoft Entra authentication |
default_language_name | sysname | Applies to: SQL Server 2012 (11.x) and later. Signifies the default language for this principal. |
default_language_lcid | int | Applies to: SQL Server 2012 (11.x) and later. Signifies the default LCID for this principal. |
allow_encrypted_value_modifications | bit | Applies to: SQL Server 2016 (13.x) and later, SQL Database. Suppresses cryptographic metadata checks on the server in bulk copy operations. This enables the user to bulk copy data encrypted using Always Encrypted, between tables or databases, without decrypting the data. The default is OFF. |
Remarks
The PasswordLastSetTime properties are available on all supported configurations of SQL Server, but the other properties are only available when SQL Server is running on Windows Server 2003 or later and both CHECK_POLICY and CHECK_EXPIRATION are enabled. See Password Policy for more information. The values of the principal_id may be reused in the case that principals have been dropped and therefore is not guaranteed to be ever-increasing.
Permissions
Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.
Examples
A: Listing all the permissions of database principals
The following query lists the permissions explicitly granted or denied to database principals.
Important
The permissions of fixed database roles do not appear in sys.database_permissions. Therefore, database principals may have additional permissions not listed here.
SELECT pr.principal_id, pr.name, pr.type_desc,
pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id;
B: Listing permissions on schema objects within a database
The following query joins sys.database_principals and sys.database_permissions to sys.objects and sys.schemas to list permissions granted or denied to specific schema objects.
SELECT pr.principal_id, pr.name, pr.type_desc,
pr.authentication_type_desc, pe.state_desc,
pe.permission_name, s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.objects AS o
ON pe.major_id = o.object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id;
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
C: Listing all the permissions of database principals
The following query lists the permissions explicitly granted or denied to database principals.
Important
The permissions of fixed database roles do not appear in sys.database_permissions
. Therefore, database principals may have additional permissions not listed here.
SELECT pr.principal_id, pr.name, pr.type_desc,
pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id;
D: Listing permissions on schema objects within a database
The following query joins sys.database_principals
and sys.database_permissions
to sys.objects
and sys.schemas
to list permissions granted or denied to specific schema objects.
SELECT pr.principal_id, pr.name, pr.type_desc,
pr.authentication_type_desc, pe.state_desc,
pe.permission_name, s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.objects AS o
ON pe.major_id = o.object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id;
See Also
Principals (Database Engine)
sys.server_principals (Transact-SQL)
Security Catalog Views (Transact-SQL)
Contained Database Users - Making Your Database Portable
Connecting to Azure SQL with Microsoft Entra authentication