2007/09/28

Listing users and all roles they're members of (SQL Server 2005)

I admit it's trivial but it took me awhile today to understand what data is shown in the sys.database_role_members catalog view, so I am posting this in case I forget.
SELECT
  DP1.name as ROLE_NAME
, DP2.name as [USER_NAME]
FROM sys.database_role_members DRM
INNER JOIN sys.database_principals DP1
  ON DRM.role_principal_id = DP1.principal_id
INNER JOIN sys.database_principals DP2
  ON DRM.member_principal_id = DP2.principal_id

No comments:

Post a Comment