SQL Server: query database user roles for all databases in server

Question

I would like to make a query for database user roles for all databases in my sql server instance. I modified a query from sp_helpuser:

select  u.name
        ,case when (r.principal_id is null) then 'public' else r.name end
        ,l.default_database_name
        ,u.default_schema_name
        ,u.principal_id
from sys.database_principals u
    left join (sys.database_role_members m join sys.database_principals r on m.role_principal_id = r.principal_id) 
        on m.member_principal_id = u.principal_id
    left join sys.server_principals l on u.sid = l.sid
    where u.type <> 'R'

How can I modify this to query from all databases? What is the link between sys.databases and sys.database_principals?

Answer 1

You could use the stored procedure sp_msforeachdb

sp_msforeachdb: This is a very useful system stored procedure that will execute any SQL script you pass to for in each of the databases on your SQL Server instance. The stored procedure just loops through the databases, which is simple to write, but it saves you from having to do it yourself.

Add a column for the database name and a [?] placeholder for the the database name and then execute the script within the sp_msforeachdb stored proc like this:

EXECUTE sp_msforeachdb 'select  ''[?]'' as DatabaseName,
             u.name
            ,case when (r.principal_id is null) then ''public'' else r.name end
            ,l.default_database_name
            ,u.default_schema_name
            ,u.principal_id
    from [?].sys.database_principals u
        left join ([?].sys.database_role_members m join [?].sys.database_principals r on m.role_principal_id = r.principal_id) 
            on m.member_principal_id = u.principal_id
        left join [?].sys.server_principals l on u.sid = l.sid
        where u.type <> ''R'''

To get this all in one table you would have to create a table in one database, we'll use the master as an example.

Create the table in the master database

Create Table master.dbo.userPermissionResults
(
....
)

Then simply add the insert statement to the start of the query

EXECUTE sp_msforeachdb 'Insert Into master.dbo.userPermissionResults select  ''[?]'' as DatabaseName,
             u.name
            ,case when (r.principal_id is null) then ''public'' else r.name end
            ,l.default_database_name
            ,u.default_schema_name
            ,u.principal_id
    from [?].sys.database_principals u
        left join ([?].sys.database_role_members m join [?].sys.database_principals r on m.role_principal_id = r.principal_id) 
            on m.member_principal_id = u.principal_id
        left join [?].sys.server_principals l on u.sid = l.sid
        where u.type <> ''R'''

You must specify the database name for the Insert statement otherwise it will try to insert the data into the current database.