Search code examples
sql-serverrolesmembership

MSSQL: List database membership roles of each user in several databases

I have several databases, which all have users under the database/security/users folder (shown on the left side of the picture:

image displaying the membership roles I am looking for

I need a query, which could list each user's role memberships (shown on the right side of the picture above, current user, called User2 has none). These are the roles I am looking for to list for each users.

Here is something similar I used before, for a different purpose (listing the server roles of logins under the /Security/Logins folder, instead of membership roles of users under the DatabaseName/Security/Users folder):

SELECT
    spU.name
    ,MAX(CASE WHEN srm.role_principal_id = 3 THEN 1 END) AS sysadmin
    ,MAX(CASE WHEN srm.role_principal_id = 4 THEN 1 END) AS securityadmin
    ,MAX(CASE WHEN srm.role_principal_id = 5 THEN 1 END) AS serveradmin
    ,MAX(CASE WHEN srm.role_principal_id = 6 THEN 1 END) AS setupadmin
    ,MAX(CASE WHEN srm.role_principal_id = 7 THEN 1 END) AS processadmin
    ,MAX(CASE WHEN srm.role_principal_id = 8 THEN 1 END) AS diskadmin
    ,MAX(CASE WHEN srm.role_principal_id = 9 THEN 1 END) AS dbcreator
    ,MAX(CASE WHEN srm.role_principal_id = 10 THEN 1 END) AS bulkadmin
FROM
    sys.server_principals AS spR
JOIN
    sys.server_role_members AS srm
ON
    spR.principal_id = srm.role_principal_id
JOIN
    sys.server_principals AS spU
ON
    srm.member_principal_id = spU.principal_id
WHERE
    spR.[type] = 'R'
    and spU.name not like '##MS%'
    and spU.name not like 'NT%'
GROUP BY
    spU.name

These are not the roles I am looking for, this is just here to make it more easier to understand in what format I would like my output to be. Anyway, the output of this query looks like this:

enter image description here

A similar output for my current task (to list the membership roles of each users under the database/security/users/ folder) would be great, but any other ways to list the membership roles is welcome.

With the membership role listing query done for each users in the current database, I want to make it work on several databases, which will be done with the help of sp_MSforeachdb. I will edit this post when I'll have managed to work it out.

Solution

  • This id the code that finally worked, if someone would need it:

    EXEC sp_MSforeachdb '
IF ''?'' NOT IN (''master'',''msdb'',''tempdb'', ''model'')
BEGIN
USE [?]
SELECT
    spU.name
    ,MAX(CASE WHEN srm.role_principal_id = 16384 THEN 1 END) AS db_owner
    ,MAX(CASE WHEN srm.role_principal_id = 16385 THEN 1 END) AS db_accessadmin
    ,MAX(CASE WHEN srm.role_principal_id = 16386 THEN 1 END) AS db_securityadmin
    ,MAX(CASE WHEN srm.role_principal_id = 16387 THEN 1 END) AS db_dlladmin
    ,MAX(CASE WHEN srm.role_principal_id = 16389 THEN 1 END) AS db_backupoperator
    ,MAX(CASE WHEN srm.role_principal_id = 16390 THEN 1 END) AS db_datareader
    ,MAX(CASE WHEN srm.role_principal_id = 16391 THEN 1 END) AS db_datawriter
    ,MAX(CASE WHEN srm.role_principal_id = 16392 THEN 1 END) AS db_denydatareader
    ,MAX(CASE WHEN srm.role_principal_id = 16393 THEN 1 END) AS db_denydatawriter
FROM
    [?].sys.database_principals AS spR
JOIN
    [?].sys.database_role_members AS srm
ON
    spR.principal_id = srm.role_principal_id
JOIN
    [?].sys.database_principals AS spU
ON
    srm.member_principal_id = spU.principal_id
GROUP BY
    spU.name
 
END'