Each user and their role in every database on the

Question

How do I get each user and their role in every database on the server?

I figure I'd start with this:

    SELECT *
FROM sys.database_role_members drm
INNER JOIN sys.database_principals rp ON drm.role_principal_id = rp.principal_id
INNER JOIN sys.database_principals mp ON drm.member_principal_id = mp.principal_id

Answer 1

I think I figured it out:

DECLARE @table TABLE (
    SERVER VARCHAR(100),
    db_name VARCHAR(100),
    db_role VARCHAR(100),
    db_user VARCHAR(100)
    )

INSERT INTO @table
EXEC sp_msforeachdb '
    USE [?];

    SELECT @@SERVERNAME SERVER,
        ''?'' db,
        rp.NAME AS database_role,
        mp.NAME AS database_user
    FROM sys.database_role_members drm
    INNER JOIN sys.database_principals rp ON drm.role_principal_id = rp.principal_id
    INNER JOIN sys.database_principals mp ON drm.member_principal_id = mp.principal_id
    ORDER BY 3
'


SELECT SERVER,
    db_role,
    db_user,
    db_name
FROM @table
WHERE db_name NOT IN (
        'master',
        'tempdb',
        'model',
        'msdb',
        'DBA_UTIL'
        )
ORDER BY 4 DESC,
    2