sys-database-role-members-transact-sql.md

title	sys.database_role_members (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	01/31/2017
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	sys.database_role_members_TSQL sys.database_role_members database_role_members_TSQL database_role_members
dev_langs	TSQL
helpviewer_keywords	sys.database_role_members catalog view
ms.assetid	ed1b019d-ca48-4db3-85df-cf6d2db591cf
caps.latest.revision	26
author	BYHAM
ms.author	rickbyh
manager	jhubbard

sys.database_role_members (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all_md]

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. To add members to a role, use the ALTER ROLE statement with the ADD MEMBER option. Join with sys.database_principals to return the names of the principal_id values.

Column name	Data type	Description
role_principal_id	int	Database principal ID of the role.
member_principal_id	int	Database principal ID of the member.

Permissions

Any user can view their own role membership. To view other role memberships requires membership in the db_securityadmin fixed database role or VIEW DEFINITION on the database.

[!INCLUDEssCatViewPerm] For 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

Security Catalog Views (Transact-SQL)
Principals (Database Engine)
Catalog Views (Transact-SQL)
ALTER ROLE (Transact-SQLL)
sys.server_role_members (Transact-SQL)