Home > Administration, Security > How get all users and their role mappings from all databases?

How get all users and their role mappings from all databases?

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name like '#Output%')
   DROP TABLE #Output

CREATE TABLE #Output (DatabaseName VARCHAR(255), RoleName VARCHAR(255), UserName VARCHAR(255))

sp_msforeachdb '
SELECT  ''?''
FROM [?].sys.database_principals  AS DR
JOIN [?].sys.database_role_members AS DRM
   ON DR.principal_id = DRM.role_principal_id
JOIN [?].sys.database_principals AS DP
   ON DP.principal_id = DRM.member_principal_id
WHERE ( <> ''dbo'')'

  FROM #Output
Categories: Administration, Security Tags:
  1. Ken
    April 4, 2016 at 11:05 am

    Excellent script – I was just looking for something like this.

    I have a favour to ask: When backing up databases on one server and restoring them to another, I sometimes have the situation where there is a SQL login, say “Ken”, and a database user “Ken” but the two are not connected. I have to run the following command to fix this:

    exec sp_change_users_login @Action=’Update_One’, @UserNamePattern=’Ken’, @LoginName=’Ken’

    Could you write a query that will show database users and the matching SQL login? If I had that then I could review the users that have a login of dbo to see if I missed anything following a database restore.

    Thanks again for the above code. It’s very useful.


  1. April 4, 2016 at 1:22 pm
  2. April 4, 2016 at 1:27 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: