"Users ?" Check. "Roles ?" Check. "Permissions ?" Check. "Show me ?" …ermmm…

\”Users ?\” Check. \”Roles ?\” Check. \”Permissions ?\” Check. \”Show me ?\” …ermmm…

Security, security, security.

Security is good, right ? Defense in depth, segregated logical access, all that stuff. Lovely.

Sadly, it\’s a bit of a pain when it comes to seeing who is a member of which user-defined database role, and what permissions that role has in any given database.

So, I knocked up the following script because, as I believe I\’ve mentioned before, Books Online is only part of the answer. I thought that if I\’d searched around for this kind of script and

came up blank, then others may well have had the same experience. And what is life for if not for sharing.

Read the comments for what it does (and, more importantly, what it DOESN\’T do). Please note, I didn\’t write all of this script (except the comments). So if you did put it on social.msdn.com, let me know and I\’ll give you a credit for it. Plagiarism is a nasty business.

Back soon….

/**
Script to extract database users, their User-Defined Role membership,
        and object permissions granted to that role.

Will only return results for database roles that have members.

Empty roles are not scripted, because it was hard,
        and if they\’re empty, there\’s no point.

Where Object = NULL and Permisson = NULL, these rows are the user / role mapping
This is there to include fixed database role membership

PLEASE NOTE: the role PUBLIC is never scripted. And in any case, you
        haven\’t applied any permissions to PUBLIC. Have you ?

**/

–User / Role Mapping

SELECT  @@SERVERNAME AS [Server Name],
                DB_NAME() AS [Database],
                a.name AS [Database User],
                b.name AS [RoleName],
                NULL AS [Object],
                NULL AS [Permission],
                GETDATE() AS [Date Extracted]
FROM sys.database_role_members c
INNER JOIN sys.database_principals a
        ON a.principal_id = c.member_principal_id
INNER JOIN sys.database_principals b
        ON b.principal_id = c.role_principal_id

UNION ALL  — OK, not pretty, but at least it\’s Excel friendly.

–Role permissions

SELECT  @@SERVERNAME AS [Server Name],
                DB_NAME() AS [Database],
                b.name AS [Database User],
                a.name AS [Role Name],
                o.NAME AS [Object],
                p.permission_name AS [Permission],
                GETDATE() AS [Date Extracted]
FROM sys.database_role_members c
LEFT OUTER JOIN sys.database_permissions p
        ON c.role_principal_id= p.grantee_principal_id
LEFT OUTER JOIN sys.database_permissions p1
        ON c.member_principal_id = p1.grantee_principal_id
LEFT OUTER JOIN sys.all_objects o
        ON p.major_id = o.OBJECT_ID
INNER JOIN sys.database_principals dp
        ON p.grantee_principal_id = dp.principal_id
INNER JOIN sys.database_principals a
        ON c.role_principal_id = a.principal_id
INNER JOIN sys.database_principals b
        ON c.member_principal_id = b.principal_id
WHERE dp.[type]=\’R\’ –Role
        AND p.major_id > 0  –system objects have a negative major_id

ORDER BY 5 ASC, 4 ASC –because I hate typing column names in an ORDER BY…