sys.database_role_members (Transact-SQL)sys.database_role_members (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

为每个数据库角色的每个成员返回一行。Returns one row for each member of each database role. 数据库用户、 应用程序角色和其他数据库角色可以是数据库角色的成员。Database users, application roles, and other database roles can be members of a database role. 若要向角色添加成员,请使用ALTER ROLE语句和ADD MEMBER选项。To add members to a role, use the ALTER ROLE statement with the ADD MEMBER option. 携手sys.database_principals要返回的名称principal_id值。Join with sys.database_principals to return the names of the principal_id values.

列名Column name 数据类型Data type 描述Description
role_principal_idrole_principal_id intint 数据库角色的主体 ID。Database principal ID of the role.
member_principal_idmember_principal_id intint 成员的数据库主体 ID。Database principal ID of the member.

权限Permissions

任何用户都可以查看自己的成员身份。Any user can view their own role membership. 若要查看其他角色成员身份要求的成员身份db_securityadmin固定的数据库角色或VIEW DEFINITION在数据库上。To view other role memberships requires membership in the db_securityadmin fixed database role or VIEW DEFINITION on the database.

目录视图中仅显示用户拥有的安全对象的元数据,或用户对其拥有某些权限的安全对象的元数据。The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

示例Example

以下查询返回的数据库角色的成员。The following query returns the members of the database roles.

SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name;  

请参阅See Also

安全性目录视图 (Transact-SQL) Security Catalog Views (Transact-SQL)
主体(数据库引擎) Principals (Database Engine)
目录视图 (Transact-SQL)Catalog Views (Transact-SQL)
更改角色 (Transact SQLL) ALTER ROLE (Transact-SQLL)
sys.server_role_members (TRANSACT-SQL)sys.server_role_members (Transact-SQL)