What about orphaned windows users?

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)

Report only:

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.