How to get orphaned logins for all databases?

Following script gives you all user database users, that do not have a server login mapped or where database user name does not match server login name.  This is follow up post to How get all users and their role mappings from all databases? I posted few days ago.

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name like '#Output%')
   DROP TABLE #Output
CREATE TABLE #Output (DatabaseName VARCHAR(255), UserLoginSID varbinary(128), ServerLoginName VARCHAR(255), DatabaseUserName VARCHAR(255), UserType VARCHAR(50))
sp_msforeachdb '
SELECT  ''?''
      , DP.sid
      , DP.type_desc
  FROM sys.database_principals DP
  LEFT JOIN sys.server_principals SP
    ON DP.sid = SP.sid
 WHERE DP.type_desc IN (''SQL_USER'',''WINDOWS_USER'')
    FROM #Output
   WHERE ServerLoginName IS NULL
      OR ServerLoginName <> DatabaseUserName
ORDER BY DatabaseName, ServerLoginName

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.


  1. That’s perfect! The only thing I did was tweak the query from #Output to filter out ‘dbo’ and ‘guest’:

    SELECT *
    FROM #Output
    WHERE (ServerLoginName IS NULL
    OR ServerLoginName DatabaseUserName)
    and DatabaseUserName not in (‘dbo’, ‘guest’)
    ORDER BY DatabaseName, ServerLoginName

    Thanks Mohit!

  2. I think that is a fair suggestion for guest. However for DBO, I would recommend leaving it in, because there can be possibility that login mapped as Database Owner has been deleted. This will let you know, if dbo maps to NULL, then your database owner property needs to be updated.

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 )

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.