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

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	ms.custom	f1_keywords	helpviewer_keywords	dev_langs	monikerRange
sys.database_role_members (Transact-SQL)	sys.database_role_members (Transact-SQL)	VanMSFT	vanto	01/31/2017	sql	system-objects	reference	ignite-2024	sys.database_role_members_TSQL sys.database_role_members database_role_members_TSQL database_role_members	sys.database_role_members catalog view	TSQL	>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

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)