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
--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