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

title	sys.database_role_members (Transact-SQL)
description	sys.database_role_members (Transact-SQL)
author	VanMSFT
ms.author	vanto
ms.date	01/31/2017
ms.prod	sql
ms.prod_service	database-engine, sql-database, synapse-analytics, pdw
ms.technology	system-objects
ms.topic	reference
f1_keywords	sys.database_role_members_TSQL sys.database_role_members database_role_members_TSQL database_role_members
helpviewer_keywords	sys.database_role_members catalog view
dev_langs	TSQL
ms.assetid	ed1b019d-ca48-4db3-85df-cf6d2db591cf
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

sys.database_role_members (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

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)