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.service	sql
ms.subservice	system-objects
ms.topic	reference
ms.custom	ignite-2025
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
monikerRange	>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

sys.database_role_members (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

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)