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. Check out this Microsoft artcile for more information:

How To Troubleshoot Orphaned Users in SQL Server

But that solution 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 T-SQL script to find and optionally fix the orphaned windows users. It script utilizes the extended stored procedure xp_logininfo to bridge the gap left by traditional methods.

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:

Report and fix:

Download Script: Find-Orphan-Windows-Users-In-SQLServer

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.