I should start off by mentioning that this post is applicable to sql server versions 2012 and up. If you have an older version of sql server, the solution discussed here will not work.
We are generally aware that a user in a database is orphaned when it does not have a matching SID record in the sys.server_principals table. This is not an issue if your databases is CONTAINED and uses database authentication. Otherwise, the user will not be able to login into the sql server instance and as a result cannot access the database even though the user has access to the database.
Generally, you will get orphaned database users after restoring a database to a different server and one or more users in the database do not have corresponding LOGIN at the instance level or has mismatched SID. Another possibility is that the login got deleted from sys.server_principals or from the Active Directory or local machine. I am sure there are other possible situations.
Microsoft has been obviously aware of the situation for a long time and has provided a stored procedure sp_change_users_login to find and optionally fix orphaned database users. But it only works for sql server logins. In other words, it does not support windows users.
What further complicates windows users is that the user may have access to sql server through membership in a windows group. So just comparing SID column between sys.database_principals and sys.server_principals will not give you accurate results. That is where extended stored procedure xp_logininfo is helpful.
So what I have below is a tsql script to find and optionally fix the orphaned windows users.
DECLARE @username NVARCHAR(500)
DECLARE @privilege NVARCHAR(500) DECLARE @sql NVARCHAR(4000) DECLARE @fix_orphaned_user BIT DECLARE @cnt INT SET @fix_orphaned_user = 0 -- set this to 1 to also fix the orphaned user DECLARE c1 CURSOR FOR SELECT dp.NAME FROM sys.database_principals dp LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid WHERE dp.type_desc = 'WINDOWS_USER' AND dp.authentication_type_desc = 'WINDOWS' AND dp.principal_id != 1 AND sp.sid IS NULL OPEN c1 FETCH c1 INTO @username WHILE @@FETCH_STATUS = 0 BEGIN SET @cnt = Isnull(@cnt, 0) + 1 EXEC xp_logininfo @acctname = @username, @option = 'all', @privilege = @privilege output IF @privilege IS NULL RAISERROR('Orphaned windows user name: %s',10,1,@username) IF @fix_orphaned_user = 1 BEGIN SET @sql = 'CREATE LOGIN [' + @username + '] FROM WINDOWS WITH DEFAULT_DATABASE = [' + DB_NAME() + ']' PRINT @sql EXEC(@sql) END FETCH c1 INTO @username END CLOSE c1 DEALLOCATE c1 IF @cnt IS NULL RAISERROR('No orphaned windows users found',10,1)
Caveat: If windows user is deleted, disabled, locked out etc. at the OS level, this script will not attempt to fix that issue.
I hope this works not only on windows but also on Linux but I have not tested it there.