User accounts in SQL Server can become orphan whenever a new database is restored or attached. Orphan account means there is no associated login for that user and the when a user logs in to SQL Server using the account which has an orphan user in the database, login account will not be able to access database objects since the user in the database is orphan. Identifying and fixing orphan accounts from all user databases is important for any database administrator. Please note: After identifying an orphan user account, you will need to use SP_Change_Users_Login System Stored Procedure to either 'Auto_Fix','Report','Update_One'. (Example: EXEC sp_change_users_login 'Update_One', 'sqlcommunity', 'sqlcommunity');
Here's the script you can use to identify orphan accounts from all databases:
--Author: Saleem Hakani (http://sqlcommunity.com)
Declare @ORPHANUSER TABLE
EXEC SP_MSFOREACHDB 'USE [?]
SELECT @@ServerName as ServerName, DB_NAME() as DBNAME,
NAME as User_Name,
CREATEDATE as Date_Created,
WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ''SQL LOGIN''
WHEN ISNTGROUP = 1 THEN ''NT GROUP''
WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ''NT LOGIN''
END) [LOGIN TYPE] FROM sys.sysusers
WHERE SID IS NOT NULL
AND SID <> 0X0
AND ISLOGIN =1
AND SID NOT IN
SELECT * FROM @ORPHANUSER