Skip to content

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

Latest commit

 

History

History
70 lines (60 loc) · 3.25 KB

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

File metadata and controls

70 lines (60 loc) · 3.25 KB

title: "sys.database_role_members (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "01/31/2017" ms.prod: "sql" ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.service: "" ms.component: "system-catalog-views" ms.reviewer: "" ms.suite: "sql" 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: "edmacauley" ms.author: "edmaca" manager: "craigg" ms.workload: "On Demand" monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"

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)