sys.database_principals (Transact-SQL)sys.database_principals (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

SQL ServerSQL Server 数据库中的每个安全主体返回一行。Returns a row for each security principal in a SQL ServerSQL Server database.

列名称Column name 数据类型Data type 说明Description
路径名name sysnamesysname 主体名称,在数据库中唯一。Name of principal, unique within the database.
principal_idprincipal_id 整形int 主体 ID,在数据库中唯一。ID of principal, unique within the database.
类别type char (1)char(1) 主体类型:Principal type:

A = 应用程序角色A = Application role

C = 映射到证书的用户C = User mapped to a certificate

E = 来自 Azure Active Directory 的外部用户E = External user from Azure Active Directory

G = Windows 组G = Windows group

K = 映射到非对称密钥的用户K = User mapped to an asymmetric key

R = 数据库角色R = Database role

S = SQL 用户S = SQL user

U = Windows 用户U = Windows user

X = Azure Active Directory 组或应用程序中的外部组X = External group from Azure Active Directory group or applications
type_desctype_desc nvarchar (60)nvarchar(60) 主体类型的说明。Description of principal type.

APPLICATION_ROLEAPPLICATION_ROLE

CERTIFICATE_MAPPED_USERCERTIFICATE_MAPPED_USER

EXTERNAL_USEREXTERNAL_USER

WINDOWS_GROUPWINDOWS_GROUP

ASYMMETRIC_KEY_MAPPED_USERASYMMETRIC_KEY_MAPPED_USER

DATABASE_ROLEDATABASE_ROLE

SQL_USERSQL_USER

WINDOWS_USERWINDOWS_USER

EXTERNAL_GROUPSEXTERNAL_GROUPS
default_schema_namedefault_schema_name sysnamesysname SQL 名称未指定架构时要使用的名称。Name to be used when SQL name does not specify a schema. 对于非 S、U 或 A 类型的主体,为 Null。Null for principals not of type S, U, or A.
create_datecreate_date datetime 主体的创建时间。Time at which the principal was created.
modify_datemodify_date datetime 上次修改主体的时间。Time at which the principal was last modified.
owning_principal_idowning_principal_id 整形int 拥有此主体的主体的 ID。ID of the principal that owns this principal. 除数据库角色以外的所有主体都必须归dbo所有。All principals except Database Roles must be owned by dbo.
sidsid varbinary (85)varbinary(85) 主体的 SID (安全标识符)。SID (Security Identifier) of the principal. SYS 和 INFORMATION SCHEMAS 为 NULL。NULL for SYS and INFORMATION SCHEMAS.
is_fixed_roleis_fixed_role bitbit 如果为 1,则该行表示与下面的某个固定数据库角色对应的条目:db_owner、db_accessadmin、db_datareader、db_datawriter、db_ddladmin、db_securityadmin、db_backupoperator、db_denydatareader、db_denydatawriter。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_typeauthentication_type 整形int 适用于: SQL Server 2012 (11.x)SQL Server 2012 (11.x)和更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

指示身份验证类型。Signifies authentication type. 下面是可能的值及其说明。The following are the possible values and their descriptions.

0:无身份验证0 : No authentication
1:实例身份验证1 : Instance authentication
2:数据库身份验证2 : Database authentication
3: Windows 身份验证3 : Windows Authentication
authentication_type_descauthentication_type_desc nvarchar (60)nvarchar(60) 适用于: SQL Server 2012 (11.x)SQL Server 2012 (11.x)和更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

身份验证类型说明。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 身份验证WINDOWS : Windows Authentication
default_language_namedefault_language_name sysnamesysname 适用于: SQL Server 2012 (11.x)SQL Server 2012 (11.x)和更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

指示此主体的默认语言。Signifies the default language for this principal.
default_language_lciddefault_language_lcid 整形int 适用于: SQL Server 2012 (11.x)SQL Server 2012 (11.x)和更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

指示此主体的默认 LCID。Signifies the default LCID for this principal.
allow_encrypted_value_modificationsallow_encrypted_value_modifications bitbit 适用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)和更高SQL 数据库SQL Database版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later, SQL 数据库SQL Database.

取消在大容量复制操作期间对服务器进行加密元数据检查。Suppresses cryptographic metadata checks on the server in bulk copy operations. 这样,用户便可以使用 Always Encrypted、在表或数据库之间对数据进行大容量复制,而无需解密数据。This enables the user to bulk copy data encrypted using Always Encrypted, between tables or databases, without decrypting the data. 默认为 OFF。The default is OFF.

备注Remarks

PasswordLastSetTime属性可用于 SQL Server 的所有受支持的配置,但其他属性仅在 SQL Server 在 Windows Server 2003 或更高版本上运行并且启用了 CHECK_POLICY 和 CHECK_EXPIRATION 时才可用。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. 如果主体已删除,因此可以重复使用 principal_id 的值,因此不一定会不断增加。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. 要查看其他用户,需要获取 ALTER ANY USER 或相关的用户权限。To see other users, requires ALTER ANY USER, or a permission on the user. 要查看用户定义的角色,需要获取 ALTER ANY ROLE 或相关的角色成员身份。To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.

示例Examples

A:列出数据库主体的所有权限A: Listing all the permissions of database principals

以下查询将列出明确对数据库主体授予或拒绝的权限。The following query lists the permissions explicitly granted or denied to database principals.

重要

固定数据库角色的权限不会出现在 sys.database_permissions 中。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:列出对数据库中架构对象的权限B: Listing permissions on schema objects within a database

以下查询将 sys.database_principals 和 sys.database_permissions 与 sys.objects 和 sys.schemas 联接,以列出对特定架构对象授予或拒绝的权限。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;  

示例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

C:列出数据库主体的所有权限C: Listing all the permissions of database principals

以下查询将列出明确对数据库主体授予或拒绝的权限。The following query lists the permissions explicitly granted or denied to database principals.

重要

固定数据库角色的权限不显示在中sys.database_permissionsThe 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:列出数据库中架构对象的权限D: Listing permissions on schema objects within a database

下面的查询联接sys.database_principalssys.database_permissions sys.objects sys.schemas以列出向特定架构对象授予或拒绝的权限。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) sys.server_principals (Transact-SQL)
Transact-sql)(安全目录视图 Security Catalog Views (Transact-SQL)
包含的数据库用户-使你的数据库可移植 Contained Database Users - Making Your Database Portable
使用 Azure Active Directory 身份验证连接到 SQL 数据库Connecting to SQL Database By Using Azure Active Directory Authentication