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

    Ken

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: