Database users can become orphaned for a few reasons. For example, a database restore to another instance or deleting the corresponding SQL login would have the effect of leaving a database user orphaned. Development/testing SQL servers may also experience this due to the number of ad-hoc updates that may occur over the years. If you are unlucky enough your production servers may even suffer from this.

SQL provides a procedure that will provide this information on a database level:

EXEC sp_change_users_login @Action='Report'

This is great and works well, however if you want to report across all of your databases, you'll need to run this against each database in turn.

So here is a script that I sometimes use to search all non-system databases which aims to find any database users that have been orphaned or do not have a corresponding SQL server login. Armed with this information, you are then in a position to deal with any that the script finds. e.g. delete the user or link it using sp_change_users_login.

--Get a list of database names in a cursor that are not system databases and are online
DECLARE cur CURSOR FAST_FORWARD FOR 
SELECT name FROM sys.databases 
WHERE database_id > 4 AND state = 0
OPEN cur  
DECLARE @SQL NVARCHAR(MAX), @DBName SYSNAME 
DECLARE @Results TABLE (DBName SYSNAME, UserName SYSNAME, UserSID VARBINARY(MAX))  
FETCH NEXT FROM cur into @DBName  
--loop through cursor, building a dynamic SQL statement that will USE the database and then retrieve the orphaned users
--Execute the dynamic sql and store the results into a table variable
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @SQL = 'USE ' + @DBName + ';SELECT ''' + @DBName + ''' AS DBName, 
UserName = name, UserSID = sid from sysusers 
WHERE issqluser = 1 AND 
(sid IS NOT NULL AND sid <> 0x0) AND 
(LEN(sid) <= 16) AND SUSER_SNAME(sid) IS NULL'     
INSERT INTO @Results 
EXEC(@SQL)  
FETCH NEXT FROM cur into @DBName  
END  
CLOSE cur 
DEALLOCATE cur  
SELECT * FROM @Results