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
GO

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

sp_msforeachdb '
INSERT INTO #Output
SELECT  ''?''
      , DR.name
      , DP.name
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 (DP.name <> ''dbo'')'
GO

SELECT *
  FROM #Output
GO

5 comments

1. 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.

   Ken

   Reply

   1. Thanks. Will get the other script up for you soon. Thanks.

      Reply

   2. Posted, please review, How to get orphaned logins for all databases?

      Thanks 🙂

      Reply
2. Pingback: How to get orphaned logins for all databases? | SQL Canada
3. Pingback: How to get orphaned login for all databases? | SQL Server Consultation

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