Using DMV to identify members roles

Some times is necessary retrieve a list of members of the roles of a database. Doing this is simple, using the DMV sys.database_role_members. Below follow an example of how to do this.

USE dbtest
GO

SELECT
roles.role_id
, roles.role_name
, roles.member_id
, principals.name AS member_name
FROM
(
SELECT
principals.name AS role_name
, roles.role_principal_id AS role_id
, roles.member_principal_id AS member_id
FROM sys.database_principals principals
join sys.database_role_members roles
ON principals.principal_id = roles.role_principal_id
) roles
JOIN sys.database_principals principals ON roles.member_id = principals.principal_id
ORDER BY member_name

---