What about orphaned windows users?

SQL Server 2012+ only/: If you're on older versions, this won't work.

Almost every DBA and some developers know that a database user becomes orphaned when its SID doesn't match anything in sys.server_principals. No big deal if you're using contained databases with database-level authentication. But for traditional server logins, those users are locked out of the instance entirely, even though they still "exist" in the database with permissions.

This usually happens when:

• You restore a database to a new server, but the instance-level LOGINS don't match
• SIDs get out of sync between database users and server logins
• Someone deletes a login from sys.server_principals (or Active Directory cleans house)

Microsoft's been dealing with this forever and gave us sp_change_users_login to find/fix it:

How To Troubleshoot Orphaned Users in SQL Server

But sp_change_users_login only works for SQL Server logins, it completely ignores Windows users.

Windows users get even trickier because they often authenticate through Windows group membership. A simple SID comparison between sys.database_principals and sys.server_principals won't catch those. That's where the extended stored procedure xp_logininfo saves the day, it validates actual Windows account existence.

That's why I wrote the T-SQL script below. It finds orphaned Windows users and optionally fixes them by leveraging xp_logininfo to bridge the gap that traditional methods miss.

-- See blog post: https://sqlpal.blogspot.com/2019/06/what-about-orphaned-windows-users.html
/*==========================================================================================
  Script Name : Find-Fix-Orphaned-Windows-Users.sql

  Purpose:
      Identifies and optionally recreates orphaned Windows users in the current database.
      Unlike sp_change_users_login (SQL logins only), this handles Windows users/groups
      using xp_logininfo to validate Windows account existence.

  How it works:
      1. Finds database users (WINDOWS_USER type) with no matching server login SID
      2. Uses xp_logininfo @option='all' to verify if Windows account still exists
      3. Reports orphaned users OR auto-creates missing server logins

  Configuration:
      @fix_orphaned_user (BIT) 
          0 = Report-only mode (default, safe)
          1 = Auto-fix by creating missing server logins

  Prerequisites:
      - Run in target database (orphaned users are DB-scoped)
      - sysadmin or equivalent to create logins (when fixing)
      - Windows Authentication environment

  Safety Notes:
      - xp_logininfo queries Active Directory/local SAM - network dependent
      - Only creates logins, doesn't modify existing database users/permissions
      - Test in non-prod first when @fix_orphaned_user=1
      - Won't fix OS-level account issues (disabled/locked/deleted accounts)

  Typical Scenarios:
      - Database restore to new server (login SIDs don't match)
      - AD cleanup removed accounts referenced by databases
      - Failover cluster with domain trust issues
==========================================================================================*/

DECLARE @username NVARCHAR(500),
        @privilege NVARCHAR(500), 
        @sql NVARCHAR(4000),
        @fix_orphaned_user BIT,
        @cnt INT = 0;

SET @fix_orphaned_user = 0;  -- 0 = Report only (SAFE), 1 = Auto-fix logins

DECLARE c1 CURSOR LOCAL FAST_FORWARD READ_ONLY 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  -- Exclude dbo
      AND sp.sid IS NULL;       -- No matching server login

OPEN c1;
FETCH c1 INTO @username;

WHILE @@FETCH_STATUS = 0 
BEGIN
    -- Count potential orphans before validation
    SET @cnt = @cnt + 1;

    /*
        xp_logininfo validates Windows account existence in AD/local SAM.
        @option='all' returns privilege level (user/group details).
        NULL result = account doesn't exist = TRUE orphan.
    */
    EXEC xp_logininfo 
        @acctname = @username, 
        @option = 'all', 
        @privilege = @privilege OUTPUT;

    -- Orphan confirmed (no Windows account found)
    IF @privilege IS NULL 
    BEGIN
        RAISERROR('Orphaned Windows user: %s', 10, 1, @username) WITH NOWAIT;
        
        -- AUTO-FIX: Create missing server login
        IF @fix_orphaned_user = 1 
        BEGIN 
            SET @sql = 'CREATE LOGIN [' + @username + 
                       '] FROM WINDOWS WITH DEFAULT_DATABASE = [' + DB_NAME() + ']';
            
            PRINT 'Creating login: ' + @sql;
            EXEC(@sql);
        END
    END;

    FETCH c1 INTO @username;
END;

CLOSE c1;
DEALLOCATE c1;

-- Final status message
IF @cnt = 0 
    RAISERROR('No potential orphaned Windows users found.', 10, 1) WITH NOWAIT;
ELSE IF @cnt > 0 AND @fix_orphaned_user = 0
    RAISERROR('%d potential orphaned Windows users (run with @fix_orphaned_user=1 to auto-fix).', 
              10, 1, @cnt) WITH NOWAIT;

Report only:

Report and fix:

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

Caveat: The script won't fix OS-level account issues (deleted/disabled/locked Windows users).

Linux: Untested but should work, let me know!