sys.database_principals (Transact-SQL) 从SQL SERVER数据库中查询安全主体

default_schema_name为主体的默认schema,默认架构

THIS TOPIC APPLIES TO: SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

Returns a row for each security principal in a SQL Server database.

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 Azure Active Directory G = Windows group K = User mapped to an asymmetric key R = Database role S = SQL user U = Windows user X = External group from Azure Active Directory 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 principals except Database Roles must be owned by dbo.
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 through SQL Server 2016. Signifies authentication type. The following are the possible values and their descriptions. 0 : No authentication 1 : Instance authentication 2 : Database authentication 3 : Windows Authentication
authentication_type_desc	nvarchar(60)	Applies to: SQL Server 2012 through SQL Server 2016. Description of the authentication type. The following are the possible values and their descriptions. NONE : No authentication INSTANCE : Instance authentication DATABASE : Database authentication WINDOWS : Windows Authentication
default_language_name	sysname	Applies to: SQL Server 2012 through SQL Server 2016. Signifies the default language for this principal.
default_language_lcid	int	Applies to: SQL Server 2012 through SQL Server 2016. Signifies the default LCID for this principal.

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.

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 SQL Data Warehouse and Parallel Data Warehouse

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;