Share via

How to get orphaned logins for all databases?

  • Article

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 GO CREATE TABLE #Output (DatabaseName VARCHAR(255), UserLoginSID varbinary(128), ServerLoginName VARCHAR(255), DatabaseUserName VARCHAR(255), UserType VARCHAR(50)) GO sp_msforeachdb ' INSERT INTO #Output SELECT ''?'' , DP.sid , SP.name , DP.name , 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'') AND DP.sid IS NOT NULL' GO SELECT * FROM #Output WHERE ServerLoginName IS NULL OR ServerLoginName DatabaseUserName ORDER BY DatabaseName, ServerLoginName GO

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