SQL Server Orphan Users: What They Are, Why They Happen, and How to Fix Them

---

Orphan users are one of the most common security and access problems a DBA encounters — and one of the most misunderstood. You restore a database to a new server, applications start throwing access denied errors, and when you investigate you discover that every SQL login in the database has no corresponding login at the server level. The users exist but they have nowhere to go. They are orphaned.

This article covers the full story: what an orphan user is, why SQL Server’s security model creates this problem in the first place, every scenario that produces orphans, how to find them, and how to fix them. It covers both the legacy stored procedures you will encounter in older scripts and environments, and the modern syntax that Microsoft now recommends for all new work.

1 How SQL Server Security Works — Logins vs Users Beginner

To understand orphan users you first need to understand how SQL Server’s two-level security model works, because the orphan problem is a direct consequence of this design.

A login is a server-level principal. It lives in the master database and controls who can connect to the SQL Server instance. Logins are defined in sys.server_principals. Think of a login as a keycard that gets you into the building.

A database user is a database-level principal. It lives inside a specific database and controls what that person can do once they are inside — which tables they can query, which stored procedures they can execute, which schemas they can access. Database users are defined in sys.database_principals inside each individual database. Think of a database user as the access level you have once you are inside a specific office on a specific floor.

The connection between them is a Security Identifier (SID) — a unique binary value that is assigned to the login when it is created. When a database user is created from that login, the same SID is stored inside the database. When someone connects, SQL Server matches the login’s SID to the database user’s SID to confirm they are the same identity.

-- The two-level model:
-- Server level (master database):
SELECT name, sid, type_desc
FROM sys.server_principals
WHERE name = 'AppUser';
-- sid: 0x3F8A12C4... (the authoritative SID for this login)

-- Database level (inside YourDatabase):
USE YourDatabase;
SELECT name, sid, type_desc
FROM sys.database_principals
WHERE name = 'AppUser';
-- sid: 0x3F8A12C4... (must match the server-level SID)

-- When these SIDs match: login works correctly
-- When they do not match: orphan user -- access denied

2 What an Orphan User Is — The SID Problem Beginner

An orphan user is a database user whose SID does not match any login at the server level. The user account exists inside the database — it has permissions, role memberships, and schema ownership — but there is no corresponding server-level login with a matching SID. The link between the two levels is broken.

When a user with an orphaned database account tries to connect, SQL Server authenticates the login at the server level just fine. But when it tries to map that login into the specific database, it cannot find a database user with a matching SID. Access is denied. The error looks like this:

-- Error seen by the user when an orphan user tries to connect:
Msg 916, Level 14, State 1
The server principal "AppUser" is not able to access the database
"YourDatabase" under the current security context.

-- Or during a restore operation when you try to fix the mapping:
Msg 15023, Level 16, State 1
User, group, or role 'AppUser' already exists in the current database.

Error 15023 is the classic orphan user error and the one you will see most often. It appears when a login exists at the server level AND a user with the same name exists in the database, but their SIDs do not match — so SQL Server refuses to map them together until the mismatch is resolved.

3 The History — Where This Problem Came From Beginner

The orphan user problem has existed since SQL Server 6.5 — it is not a bug, it is an architectural consequence of how the two-level security model works. When a SQL Server login is created, SQL Server generates a new SID for it. That SID is unique to that specific SQL Server instance. If you create a login called AppUser on Server A, it gets SID 0x3F8A12C4.... If you create a login called AppUser on Server B, it gets a completely different SID — 0x8B7E41D2... — even though the name is identical.

When you back up a database from Server A and restore it to Server B, the database user AppUser still has the original SID from Server A (0x3F8A12C4...). But the login on Server B has a different SID (0x8B7E41D2...). The names match but the SIDs do not. The user is now orphaned.

This was not a problem when databases never moved between servers. But the moment database migration, disaster recovery, development environment refreshes, and Always On Availability Groups became common operations — which is every modern SQL Server environment — orphan users became an everyday DBA reality.

Microsoft introduced sp_change_users_login in SQL Server 7.0 as the fix mechanism. It was the standard tool for resolving orphans for over a decade. In SQL Server 2008, Microsoft deprecated it in favor of the cleaner ALTER USER ... WITH LOGIN syntax, but the old stored procedure still works in SQL Server 2022 — meaning you will encounter it in scripts, documentation, and forums constantly, even today.

4 Every Scenario That Creates Orphan Users Beginner

5 Why Orphan Users Cause Problems Beginner

The immediate problem is obvious — application users cannot access the database. But orphan users cause several categories of problems beyond simple access denial:

• Application outage after restore or migration. Any restore or failover operation that is not followed by an orphan fix procedure will produce immediate access errors for all SQL Auth users. This is the most common source of post-migration incidents that wake DBAs up at 3 AM.
• Silent permission loss. In some configurations, especially after version upgrades, an orphaned user may partially work — the user can connect but finds that certain permissions are missing or behaving unexpectedly. This is harder to diagnose than a flat access denied error.
• Blocked database ownership changes. If the database owner (dbo) is an orphaned user, certain administrative operations on the database may fail with confusing error messages.
• Security drift. Orphaned users accumulate over time in environments without a fix process. An orphaned user still holds all its permissions inside the database — it just cannot be accessed through its original login. If that login is later recreated with a different purpose and the orphan happens to get re-linked, it inherits those permissions unexpectedly.
• Restore validation failures. Post-restore validation that checks application connectivity will fail if orphans are not fixed before testing. Orphan fixing should be a mandatory step in every restore runbook.

6 Finding Orphans — Current Database Beginner

Always run orphan detection in the context of the specific database you are investigating. Running it in master returns no results and is the most common mistake when people first try to use these queries.

Legacy Method — sp_change_users_login ‘Report’ Deprecated

-- Run in the context of the database you want to check
USE YourDatabase;
GO
EXEC sp_change_users_login 'Report';
GO
-- Returns: UserName, UserSID
-- Any user listed here is an orphan -- no matching login SID at server level
-- Works on SQL Server 2005 through 2022 but is deprecated -- see modern alternative below

Modern Method — sys.database_principals Recommended

-- Modern equivalent using catalog views -- works on SQL Server 2005+
-- Run in the context of the database you want to check
USE YourDatabase;
GO
SELECT
    dp.name                  AS OrphanedUser,
    dp.type_desc             AS UserType,
    dp.sid                   AS DatabaseSID,
    dp.create_date           AS CreatedDate,
    dp.default_schema_name   AS DefaultSchema
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE sp.sid IS NULL
  AND dp.authentication_type_desc = 'INSTANCE'   -- SQL Auth users only
  AND dp.type IN ('S', 'U')                       -- SQL users and Windows users
  AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys');
GO
-- Returns same information as sp_change_users_login 'Report' plus extra metadata
-- 'INSTANCE' authentication = SQL Server authenticated users (not Windows, not certificates)

7 Finding Orphans — All Databases on the Server Beginner

After a server migration or a full instance restore, orphans may exist across many databases. Checking each one individually is impractical. These scripts sweep all databases at once.

Legacy Server-Wide Sweep — sp_MSforeachdb Deprecated

-- Server-wide orphan sweep using sp_MSforeachdb
-- sp_MSforeachdb is an undocumented stored procedure -- use with awareness
-- Runs sp_change_users_login 'Report' in every database on the server

EXEC sp_MSforeachdb "USE [?]; EXEC sp_change_users_login 'report'";
GO
-- Output: one result set per database, labeled with the database name
-- Any rows in the result set = orphaned users in that database
-- Databases with no orphans return empty result sets

Modern Server-Wide Sweep — sys.database_principals Recommended

-- Modern server-wide orphan detection without deprecated procedures
-- Uses sp_executesql to query each database's catalog views

CREATE TABLE #OrphanReport (
    DatabaseName   SYSNAME        NOT NULL,
    OrphanedUser   SYSNAME        NOT NULL,
    UserType       VARCHAR(60)    NOT NULL,
    DatabaseSID    VARBINARY(85)  NOT NULL,
    CreatedDate    DATETIME       NOT NULL
);

DECLARE @dbname SYSNAME;
DECLARE @sql    NVARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
    SELECT name FROM sys.databases
    WHERE state_desc = 'ONLINE'
    AND   name NOT IN ('master', 'tempdb', 'model', 'msdb');

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'
    USE ' + QUOTENAME(@dbname) + N';
    INSERT INTO #OrphanReport (DatabaseName, OrphanedUser, UserType, DatabaseSID, CreatedDate)
    SELECT
        DB_NAME()         AS DatabaseName,
        dp.name           AS OrphanedUser,
        dp.type_desc      AS UserType,
        dp.sid            AS DatabaseSID,
        dp.create_date    AS CreatedDate
    FROM sys.database_principals dp
    LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
    WHERE sp.sid IS NULL
      AND dp.authentication_type_desc = ''INSTANCE''
      AND dp.type IN (''S'', ''U'')
      AND dp.name NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'');';

    EXEC sp_executesql @sql;
    FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;
DEALLOCATE dbcursor;

-- Show results
SELECT * FROM #OrphanReport ORDER BY DatabaseName, OrphanedUser;

DROP TABLE #OrphanReport;

8 The Modern Query Using sys.database_principals Intermediate

Understanding what the detection query is actually doing helps you trust its output and extend it for your own environment. This section explains the catalog views and the SID matching logic.

-- Understanding the orphan detection query components:

-- sys.database_principals: all security principals inside a specific database
-- Includes: SQL users, Windows users, database roles, application roles, certificates
-- Key columns for orphan detection:
--   name:                   the user's name inside the database
--   sid:                    the SID stored inside the database (from when user was created)
--   type:                   S = SQL user, U = Windows user, G = Windows group, R = role
--   authentication_type_desc: INSTANCE = SQL Server auth, WINDOWS = Windows auth,
--                              NONE = no login (contained database users), CERTIFICATE, etc.

-- sys.server_principals: all security principals at the server level (in master)
-- Key columns:
--   name: login name
--   sid:  the SID assigned when this login was created on THIS server instance

-- The LEFT JOIN + WHERE sp.sid IS NULL pattern:
-- For each database user, try to find a server login with the same SID
-- If no match is found (sp.sid IS NULL), the user is orphaned

-- Why filter authentication_type_desc = 'INSTANCE':
-- Windows users (WINDOWS auth) are authenticated against Active Directory
-- Their SID is their Active Directory SID -- consistent across all servers in the domain
-- Windows users cannot be orphaned in the same way as SQL users
-- Only SQL Server authenticated users (INSTANCE) have instance-specific SIDs

-- Example: what a healthy mapping looks like vs an orphan
SELECT
    dp.name             AS db_user,
    dp.sid              AS db_sid,
    sp.name             AS server_login,
    sp.sid              AS server_sid,
    CASE WHEN sp.sid IS NULL THEN 'ORPHANED' ELSE 'OK' END AS status
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.authentication_type_desc = 'INSTANCE'
AND   dp.type = 'S'
AND   dp.name NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys');

9 Fix Option 1 — ALTER USER (Modern, Recommended) Beginner

The modern fix for orphan users is ALTER USER ... WITH LOGIN. This is clean, supported, has no deprecation warning, and does exactly one thing: re-links a database user to a server login. The login must already exist at the server level before running this command.

-- Modern fix: ALTER USER ... WITH LOGIN
-- The login must already exist in sys.server_principals before running this

-- Fix a single orphaned user
USE YourDatabase;
GO
ALTER USER AppUser WITH LOGIN = AppUser;
GO
-- This re-links the database user 'AppUser' to the server login 'AppUser'
-- SQL Server updates the SID in sys.database_principals to match the login
-- All existing permissions, role memberships, and schema ownership are preserved

-- Verify the fix worked
SELECT
    dp.name             AS db_user,
    dp.sid              AS db_sid,
    sp.name             AS server_login,
    sp.sid              AS server_sid
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.name = 'AppUser';
-- Both SIDs should now match

Fix All Orphans in a Database Using ALTER USER

-- Modern cursor-based fix for all orphans in the current database
-- Only fixes users where a matching login name already exists at server level
-- Skips users with no corresponding login (they need a login created first)

USE YourDatabase;
GO

DECLARE @UserName SYSNAME;
DECLARE @SQL      NVARCHAR(500);

DECLARE OrphanCursor CURSOR FOR
    SELECT dp.name
    FROM sys.database_principals dp
    LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
    WHERE sp.sid IS NULL
      AND dp.authentication_type_desc = 'INSTANCE'
      AND dp.type = 'S'
      AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
      -- Only include users where a login with the same name exists
      -- (can't fix orphans where no matching login exists yet)
      AND EXISTS (
            SELECT 1 FROM sys.server_principals spl
            WHERE spl.name = dp.name
          );

OPEN OrphanCursor;
FETCH NEXT FROM OrphanCursor INTO @UserName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'ALTER USER ' + QUOTENAME(@UserName)
             + N' WITH LOGIN = ' + QUOTENAME(@UserName) + N';';
    PRINT 'Fixing: ' + @UserName;
    EXEC sp_executesql @SQL;
    FETCH NEXT FROM OrphanCursor INTO @UserName;
END

CLOSE OrphanCursor;
DEALLOCATE OrphanCursor;
GO

10 Fix Option 2 — sp_change_users_login (Legacy, Deprecated) Intermediate

You will encounter sp_change_users_login constantly in older scripts, forum posts, and documentation written before 2008. It still works on SQL Server 2022 but Microsoft has explicitly marked it for removal. Understand what it does so you can read legacy scripts correctly, then use ALTER USER for any new work you write.

The Three Actions

Action	What It Does	Modern Equivalent
'Report'	Lists all orphaned users in the current database (no changes made)	LEFT JOIN query against sys.database_principals and sys.server_principals
'Update_One'	Re-links a specific database user to a specific existing server login	ALTER USER username WITH LOGIN = loginname
'Auto_Fix'	Re-links user to login of same name. If no login exists, creates one with the specified password	Create login first, then ALTER USER username WITH LOGIN = loginname

-- LEGACY sp_change_users_login examples -- for reference only
-- Use ALTER USER for new scripts

-- Report: list orphans in current database
USE YourDatabase;
GO
EXEC sp_change_users_login 'Report';
GO

-- Update_One: re-link a specific user to an existing login (login must exist first)
USE YourDatabase;
GO
EXEC sp_change_users_login 'Update_One', 'AppUser', 'AppUser';
GO
-- Equivalent modern syntax:
-- ALTER USER AppUser WITH LOGIN = AppUser;

-- Auto_Fix: re-link user to login of same name, create login if it doesn't exist
-- WARNING: 'Auto_Fix' creates a new login with the specified password if one doesn't exist
-- Never use Auto_Fix blindly in a security-sensitive environment --
-- verify what login it will create before running
USE YourDatabase;
GO
EXEC sp_change_users_login 'Auto_Fix', 'AppUser', NULL, 'TempPassword123!';
GO
-- If login 'AppUser' already exists: re-links the SID (password is ignored)
-- If login 'AppUser' does not exist: creates it with 'TempPassword123!' and links it

11 Production Fix Scripts Intermediate

The following three stored procedures are production-ready DBA tools for handling orphan users. They use the legacy sp_change_users_login syntax — which still works on SQL Server 2022 — and are provided as-is for environments where they are already in use. The modern ALTER USER equivalents are noted alongside each one.

SP 1: Fix All Orphans — Map to Existing Login of Same Name

Links every orphaned user to the existing server login of the same name. The login must already exist. Use this after a database restore where you have already created the matching logins on the new server.

-- spDBA_FixOrphanUsers
-- Maps all orphaned database users to the server login of the same name
-- Prerequisite: all required logins must already exist at the server level
-- The dbo user is handled specially -- reassigned to sa if orphaned

-- CREATE PROCEDURE dbo.spDBA_FixOrphanUsers AS

DECLARE @username VARCHAR(25);
DECLARE GetOrphanUsers CURSOR FOR
    SELECT name AS UserName
    FROM sysusers
    WHERE issqluser   = 1
      AND sid         IS NOT NULL
      AND sid        <> 0x0
      AND SUSER_SNAME(sid) IS NULL   -- no matching login = orphan
    ORDER BY name;

OPEN GetOrphanUsers;
FETCH NEXT FROM GetOrphanUsers INTO @username;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @username = 'dbo'
        EXEC sp_changedbowner 'sa';   -- dbo orphan: reassign database owner to sa
    ELSE
        EXEC sp_change_users_login 'update_one', @username, @username;
        -- Modern equivalent: EXEC sp_executesql
        --   N'ALTER USER ' + QUOTENAME(@username) + N' WITH LOGIN = ' + QUOTENAME(@username);
    FETCH NEXT FROM GetOrphanUsers INTO @username;
END

CLOSE GetOrphanUsers;
DEALLOCATE GetOrphanUsers;
GO

SP 2: Fix All Orphans — Create Login If It Does Not Exist

Attempts to fix all orphans. If no matching server login exists, creates one using the username as the password. Use this when logins do not yet exist on the destination server. Change all passwords immediately after running.

-- spDBA_FixOrphanUsersPassword
-- Like SP1 but uses Auto_Fix -- creates missing logins with password = username
-- IMPORTANT: change all passwords created by this script immediately
-- Only use in controlled environments -- never in production without password reset

-- CREATE PROCEDURE dbo.spDBA_FixOrphanUsersPassword AS

DECLARE @username VARCHAR(25);
DECLARE @password VARCHAR(25);

DECLARE GetOrphanUsers CURSOR FOR
    SELECT name AS UserName
    FROM sysusers
    WHERE issqluser   = 1
      AND sid         IS NOT NULL
      AND sid        <> 0x0
      AND SUSER_SNAME(sid) IS NULL
    ORDER BY name;

OPEN GetOrphanUsers;
FETCH NEXT FROM GetOrphanUsers INTO @username;
SET @password = @username;   -- temporary password = username -- CHANGE IMMEDIATELY

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @username = 'dbo'
        EXEC sp_changedbowner 'sa';
    ELSE
        EXEC sp_change_users_login 'Auto_Fix', @username, NULL, @password;
        -- Auto_Fix: if login exists, re-links (password ignored)
        --           if login does not exist, creates it with @password
    FETCH NEXT FROM GetOrphanUsers INTO @username;
END

CLOSE GetOrphanUsers;
DEALLOCATE GetOrphanUsers;
GO

SP 3: Drop All Orphaned Users

Drops every orphaned user from the database. Use when you want a clean slate — for example on a development environment refresh where you will recreate users from scratch. All permissions held by dropped users are permanently removed.

-- spDBA_DropOrphanUsers
-- Drops all orphaned users from the current database
-- WARNING: all permissions held by dropped users are permanently lost
-- Use on dev/test environments or when you intend to recreate users from scratch

-- CREATE PROCEDURE dbo.spDBA_DropOrphanUsers AS

DECLARE @username VARCHAR(25);

DECLARE GetOrphanUsers CURSOR FOR
    SELECT name AS UserName
    FROM sysusers
    WHERE issqluser   = 1
      AND sid         IS NOT NULL
      AND sid        <> 0x0
      AND SUSER_SNAME(sid) IS NULL
    ORDER BY name;

OPEN GetOrphanUsers;
FETCH NEXT FROM GetOrphanUsers INTO @username;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @username = 'dbo'
        EXEC sp_changedbowner 'sa';   -- cannot drop dbo -- reassign to sa instead
    ELSE
        EXEC sp_dropuser @username;
        -- Modern equivalent: DROP USER username;
    FETCH NEXT FROM GetOrphanUsers INTO @username;
END

CLOSE GetOrphanUsers;
DEALLOCATE GetOrphanUsers;
GO

Server-Wide Detection and Auto-Fix

-- Server-wide orphan detection (legacy)
-- Runs sp_change_users_login 'Report' across all databases
EXEC sp_MSforeachdb "USE [?]; EXEC sp_change_users_login 'report'";
GO

-- Server-wide auto-fix with login creation
-- Runs Auto_Fix across all databases -- creates logins where they don't exist
-- CHANGE ALL PASSWORDS after running
EXEC sp_MSforeachdb "
USE [?];
DECLARE @username VARCHAR(25);
DECLARE @password VARCHAR(25);
DECLARE GetOrphanUsers CURSOR FOR
    SELECT name FROM sysusers
    WHERE issqluser = 1 AND sid IS NOT NULL
    AND sid <> 0x0 AND SUSER_SNAME(sid) IS NULL
    ORDER BY name;
OPEN GetOrphanUsers;
FETCH NEXT FROM GetOrphanUsers INTO @username;
SET @password = @username;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @username='dbo' EXEC sp_changedbowner 'sa';
    ELSE EXEC sp_change_users_login 'Auto_Fix', @username, NULL, @password;
    FETCH NEXT FROM GetOrphanUsers INTO @username;
END
CLOSE GetOrphanUsers;
DEALLOCATE GetOrphanUsers;
";

12 Windows vs SQL Auth — Different Fix Approaches Intermediate

The orphan problem behaves differently for Windows Authentication users and SQL Authentication users. Understanding this distinction prevents wasted troubleshooting time.

SQL Authentication Users

SQL Auth logins get a new SID every time they are created on a new server instance. This is the primary source of orphan users. The fix is ALTER USER ... WITH LOGIN after the login exists on the destination server.

Windows Authentication Users

Windows Auth logins use the Active Directory SID, which is consistent across all servers in the same domain. A Windows user restored to any server in the same AD domain will automatically re-link correctly — no orphan fix needed. This is one of the key security advantages of Windows Authentication in enterprise environments.

-- Windows Auth users: check if they are genuinely orphaned
-- (rare -- usually means the AD account was deleted or the server is not domain-joined)
SELECT
    dp.name             AS OrphanedWindowsUser,
    dp.type_desc,
    dp.sid
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE sp.sid IS NULL
  AND dp.authentication_type_desc = 'WINDOWS'
  AND dp.type IN ('U', 'G')     -- Windows users and groups
  AND dp.name NOT IN ('dbo','guest');

-- If Windows users appear orphaned:
-- 1. Confirm the server is joined to the same Active Directory domain
-- 2. Confirm the AD account still exists and has not been deleted or renamed
-- 3. Add the Windows login at the server level: CREATE LOGIN [DOMAIN\User] FROM WINDOWS
-- 4. No ALTER USER needed -- the SID will match automatically

13 Special Cases — AG Secondaries, Log Shipping, Read-Only Databases Advanced

Standard orphan fix scripts do not work on read-only databases. Always On AG secondary replicas in read-only mode and log shipping secondaries are both read-only — you cannot run ALTER USER or sp_change_users_login against them. The fix must happen at the server level.

-- AG / Log Shipping secondaries: fix orphans at the SERVER level, not the database level
-- You cannot modify sys.database_principals on a read-only database
-- The solution: ensure logins on the secondary have the SAME SID as the primary

-- Step 1: Script the login from the PRIMARY with its original SID
-- Run on the primary replica:
SELECT
    name,
    password_hash,
    sid,
    default_database_name,
    is_policy_checked,
    is_expiration_checked
FROM sys.sql_logins
WHERE name = 'AppUser';

-- Step 2: Create the login on the SECONDARY with the same SID
-- Run on each secondary replica:
CREATE LOGIN AppUser
    WITH PASSWORD     = 0x02004... HASHED,  -- use the hashed password from step 1
         SID          = 0x3F8A12C4...,       -- use the exact SID from the primary
         DEFAULT_DATABASE = master,
         CHECK_POLICY = OFF,
         CHECK_EXPIRATION = OFF;
-- When the SID matches, the database user on the secondary links automatically
-- No ALTER USER needed -- the SID in the database already matches

-- BEST PRACTICE: use sp_help_revlogin (see Section 14) to script all logins
-- with their original SIDs as part of server setup
-- This prevents orphans on secondaries entirely

-- Verify the SID matches between primary and secondary
-- Run on the secondary:
USE master;
SELECT name, sid FROM sys.sql_logins WHERE name = 'AppUser';

USE YourDatabase;   -- this runs against the secondary's read-only copy
SELECT name, sid FROM sys.database_principals WHERE name = 'AppUser';

-- Both SIDs must be identical hexadecimal values
-- If they match: no orphan -- user will work after failover
-- If they differ: drop and recreate the login on the secondary with the correct SID

14 Preventing Orphans — sp_help_revlogin and SID Preservation Advanced

The best way to handle orphan users is to prevent them from occurring. The primary prevention tool is sp_help_revlogin — a Microsoft-provided script that generates CREATE LOGIN statements with the original SID values preserved. When you use these generated scripts to create logins on the destination server, the SIDs match the database users exactly and no orphans are produced.

-- sp_help_revlogin: Microsoft's login scripting procedure
-- Source: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances
-- Install on the SOURCE server (run in master):

-- After installing, generate the login scripts:
USE master;
GO
EXEC sp_help_revlogin;
GO
-- Output: a set of CREATE LOGIN statements with hashed passwords and original SIDs
-- Copy the output and run it on the DESTINATION server
-- The logins will have the same SIDs as the source -- no orphans after restore

-- Example of generated output (what sp_help_revlogin produces):
-- IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'AppUser')
-- BEGIN
--     CREATE LOGIN [AppUser]
--         WITH PASSWORD = 0x02004B7F... HASHED,
--              SID      = 0x3F8A12C4ABCD...,
--              DEFAULT_DATABASE = [master],
--              CHECK_POLICY = OFF;
-- END
-- GO
-- The SID = 0x3F8A12C4... is the critical part --
-- this matches the SID stored in the database user

-- Alternative: script individual logins with SID manually (no sp_help_revlogin needed)
-- Useful for migrating specific logins without installing sp_help_revlogin

USE master;
GO
SELECT
    'CREATE LOGIN ' + QUOTENAME(name)
  + ' WITH PASSWORD = ' + CONVERT(VARCHAR(MAX), password_hash, 1) + ' HASHED'
  + ', SID = '          + CONVERT(VARCHAR(MAX), sid, 1)
  + ', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name)
  + ', CHECK_POLICY = '  + CASE is_policy_checked  WHEN 1 THEN 'ON' ELSE 'OFF' END
  + ', CHECK_EXPIRATION = ' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
  + ';'                  AS CreateLoginScript
FROM sys.sql_logins
WHERE name NOT IN ('sa', '##MS_PolicyEventProcessingLogin##', '##MS_AgentSigningCertificate##')
ORDER BY name;
-- Run the generated CREATE LOGIN statements on the destination server
-- The SID clause is the key -- it preserves the original SID and prevents orphans

15 Post-Restore Validation Checklist Beginner

This checklist should be part of every database restore runbook, migration plan, and AG/log shipping failover procedure. Run it after every restore operation before testing application connectivity.

1. Run orphan detection against every restored database before testing applications.
2. Confirm all required logins exist on the destination server before running any fix script.
3. Use ALTER USER for the fix — not sp_change_users_login for new scripts.
4. Handle the dbo user separately — if dbo is orphaned, use sp_changedbowner 'sa' to reassign ownership.
5. Re-run the detection query after fixing — confirm zero orphans remain before declaring success.
6. Test application connectivity with a representative login before handing back to the team.
7. Check all databases after a server-level migration — orphans may exist in multiple databases simultaneously.
8. For AG and log shipping: fix logins on all replicas with matching SIDs before failover, not after.

-- Quick post-restore validation: run this immediately after any restore
-- Confirms zero orphans in the restored database

USE [RestoredDatabase];
GO

SELECT
    CASE WHEN COUNT(*) = 0
         THEN 'PASS: No orphaned users found'
         ELSE 'FAIL: ' + CONVERT(VARCHAR(10), COUNT(*)) + ' orphaned user(s) found'
    END AS OrphanCheck,
    COUNT(*) AS OrphanCount
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE sp.sid IS NULL
  AND dp.authentication_type_desc = 'INSTANCE'
  AND dp.type = 'S'
  AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys');
GO
-- PASS: ready for application testing
-- FAIL: run ALTER USER fix script before testing

References

• Microsoft Docs — Troubleshoot Orphaned Users (SQL Server)
• Microsoft Docs — sp_change_users_login (Deprecated, use ALTER USER)
• Microsoft Docs — ALTER USER (T-SQL)
• Microsoft — How to Transfer Logins and Passwords Between Instances (sp_help_revlogin)
• Microsoft Docs — sys.database_principals
• Microsoft Docs — sys.server_principals
• SQL Authority — Fix Error 15023: User Already Exists in Current Database
• SQLYARD — SQL Server Always On Availability Groups: The Complete DBA Guide
• SQLYARD — SQL Server DBA Health Check Toolkit