List users associated to SQL Roles - T-SQL

Shows a list of users associated to database roles.

SELECT DP1.[name] AS DatabaseRoleName,  
   isnull (DP2.[name], 'No members') AS DatabaseUserName  
   , DP2.[name]
   , DP2.[type_desc]
   , DP2.create_date
   , DP2.authentication_type
   , DP2.[sid]
 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'  

-- R indicates 'DATABASE_ROLE' , there's also S, SQL_USER

ORDER BY DP1.name;