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))
INSERT INTO #Output
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'')'
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.
Thanks. Will get the other script up for you soon. Thanks.
Posted, please review, How to get orphaned logins for all databases?