List Database Roles and Members with Server Login

This Transact-SQL script list all roles of the current database with their members.
Additonal the associated SQL Server login is listed.

Works with SQL Server 2005 and higher versions in all editions.

Links:
sys.database_role_members: http://msdn.microsoft.com/en-us/library/ms187328.aspx
sys.database_principals: http://msdn.microsoft.com/en-us/library/ms187328.aspx

SQL

Skript bearbeiten|Remove

-- List Database Roles and Members with Server Login
SELECT ROL.name AS RoleName
      ,MEM.name AS MemberName
      ,MEM.type_desc AS MemberType
      ,MEM.default_schema_name AS DefaultSchema
      ,SP.name AS ServerLogin
FROM sys.database_role_members AS DRM
     INNER JOIN sys.database_principals AS ROL
         ON DRM.role_principal_id = ROL.principal_id
     INNER JOIN sys.database_principals AS MEM
         ON DRM.member_principal_id = MEM.principal_id
     INNER JOIN sys.server_principals AS SP
         ON MEM.[sid] = SP.[sid]
ORDER BY RoleName
        ,MemberName;

-- List Database Roles and Members with Server Login 
SELECT ROL.name AS RoleName 
      ,MEM.name AS MemberName 
      ,MEM.type_desc AS MemberType 
      ,MEM.default_schema_name AS DefaultSchema 
      ,SP.name AS ServerLogin 
FROM sys.database_role_members AS DRM 
     INNER JOIN sys.database_principals AS ROL 
         ON DRM.role_principal_id = ROL.principal_id 
     INNER JOIN sys.database_principals AS MEM 
         ON DRM.member_principal_id = MEM.principal_id 
     INNER JOIN sys.server_principals AS SP 
         ON MEM.[sid] = SP.[sid] 
ORDER BY RoleName 
        ,MemberName;