DBA Scripts: Fix Orphaned Users

Peter Whyte

–

Part of the DBA-Tools Project.

From Finding Orphaned Users to Actually Fixing Them

Get Orphaned Users answers “do I have this problem”. This script answers the next question: “what do I actually run to fix it”. After a database restore onto a server where the matching logins weren’t created with the same SID, database users can end up orphaned, their SID no longer matches any login on the instance, so authentication fails even though the user object still exists in the database.

Fix-OrphanedUsers scans every online, writable user database, finds every orphaned user, and generates the ALTER USER ... WITH LOGIN statement to remap each one, wherever a login of the same name actually exists to map it to. Where one doesn’t, it says so explicitly instead of generating a statement that would fail.

Why Orphaned Users Matters

  • Orphaned users fail silently until someone tries to log in. The database looks fine; nothing errors until an application or person actually authenticates as that user and gets denied.
  • It’s a near-guaranteed side effect of certain migration patterns. Restoring a database onto a new server without preserving login SIDs (via Generate-LoginScript.sql WITH SID = ...) orphans every SQL-authenticated user in it.
  • The fix is mechanical but risky to get wrong at scale. Hand-mapping users one by one across dozens of databases is slow and error-prone; generating the DDL and reviewing it before running is faster and safer.
  • Not every orphan can be auto-fixed, and the script is explicit about which ones it can’t, rather than guessing.

When to Run This Script

  • On the TARGET server, immediately after restoring databases during a migration, once the matching logins have been created
  • After any restore where you’re not certain the source and target logins share the same SIDs
  • As a periodic check on a server that regularly receives restored databases (refresh-from-prod environments are a common repeat offender)
  • Following up a Get Orphaned Users finding, to generate the actual fix rather than fixing users by hand

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Fix-OrphanedUsers
Category    : migration
Purpose     : Generate ALTER USER statements to re-map orphaned database users to their
              matching server-level logins across all user databases. Run on TARGET after
              databases are restored and logins are created.
Author      : Peter Whyte (https://sqldba.blog)
Requires    : VIEW ANY DATABASE, VIEW SERVER STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

/*
  DESIGN: After restoring databases from a source server, SQL logins are re-created with the
  same SID (via Generate-LoginScript.sql WITH SID = ...). This means SQL-authenticated users
  are NOT orphaned — their SID in sys.database_principals matches the new login's SID.

  Windows-authenticated users are also fine because the AD SID never changes.

  The orphan case that CAN occur:
    - SQL logins created without SID preservation (e.g. the old login was dropped and re-created
      and the SID therefore differs from what is stored in the restored database).
    - Databases restored from an environment where logins no longer exist on the new server.

  This script generates ALTER USER ... WITH LOGIN statements for any user in any database whose
  SID does not match any login on this instance. It assumes login name = user name (common case).
  Review the output before executing — not every orphan can be fixed with a simple name match.

  To EXECUTE the output directly:
    Uncomment the EXEC sp_executesql lines below (currently commented for safety).
*/

DECLARE @ddl  nvarchar(max) = N'';
DECLARE @crlf nchar(2)      = CHAR(13) + CHAR(10);
DECLARE @sql  nvarchar(max);
DECLARE @dbname nvarchar(128);

SET @ddl = @ddl
    + N'-- ================================================================' + @crlf
    + N'-- Orphaned User Fix Script' + @crlf
    + N'-- Target  : ' + @@SERVERNAME + @crlf
    + N'-- Generated: ' + CONVERT(nvarchar(30), GETDATE(), 120) + @crlf
    + N'-- Review before executing. Each line maps a database user to a login' + @crlf
    + N'-- by name — verify the name match is correct first.' + @crlf
    + N'-- ================================================================' + @crlf + @crlf;

-- Temp table to collect orphans across all databases
IF OBJECT_ID('tempdb..#orphans') IS NOT NULL DROP TABLE #orphans;
CREATE TABLE #orphans (
    database_name nvarchar(128),
    user_name     nvarchar(128),
    user_type     char(1),
    user_sid      varbinary(85)
);

-- Loop all user databases using sp_MSforeachdb alternative (cursor-based for reliability)
DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE database_id > 4
      AND state_desc = N'ONLINE'
      AND is_read_only = 0
    ORDER BY name;

OPEN db_cur;
FETCH NEXT FROM db_cur INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'
        INSERT INTO #orphans (database_name, user_name, user_type, user_sid)
        SELECT
            N''' + REPLACE(@dbname, N'''', N'''''') + N''',
            dp.name,
            dp.type,
            dp.sid
        FROM [' + @dbname + N'].sys.database_principals dp
        WHERE dp.type IN (''S'', ''U'', ''G'')   -- SQL, Windows user, Windows group
          AND dp.authentication_type_desc = N''INSTANCE'' -- mapped to a server login
          AND dp.sid IS NOT NULL
          AND dp.name NOT IN (N''dbo'', N''guest'', N''sys'', N''INFORMATION_SCHEMA'')
          AND dp.name NOT LIKE N''##%''
          AND dp.sid NOT IN (
              SELECT sid FROM sys.server_principals
              WHERE type IN (''S'', ''U'', ''G'')
          );';

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

CLOSE db_cur;
DEALLOCATE db_cur;

-- Build output
SELECT @ddl = @ddl
    + N'-- ' + o.database_name + N': ' + CAST(cnt.n AS nvarchar(10)) + N' orphan(s)' + @crlf
FROM #orphans o
INNER JOIN (SELECT database_name, COUNT(*) AS n FROM #orphans GROUP BY database_name) cnt
    ON cnt.database_name = o.database_name
GROUP BY o.database_name, cnt.n
ORDER BY o.database_name;

SET @ddl = @ddl + @crlf;

SELECT @ddl = @ddl
    + N'USE [' + o.database_name + N'];' + @crlf
    + CASE
        WHEN EXISTS (
            SELECT 1 FROM sys.server_principals sp
            WHERE sp.name = o.user_name AND sp.type IN ('S','U','G')
        )
        THEN N'ALTER USER [' + o.user_name + N'] WITH LOGIN = [' + o.user_name + N'];' + @crlf
        ELSE N'-- Cannot auto-fix: no login named [' + o.user_name + N'] found. Create the login first or map manually.' + @crlf
      END
    + N'GO' + @crlf + @crlf
FROM #orphans o
ORDER BY o.database_name, o.user_name;

IF NOT EXISTS (SELECT 1 FROM #orphans)
    SET @ddl = @ddl + N'-- No orphaned users found. All database users map to a valid server login.' + @crlf;

DROP TABLE #orphans;

SELECT @ddl AS ddl;

Loops every online, non-read-only user database via cursor, collects any database user whose SID doesn’t match a server-level login, then builds a reviewable .sql script: an ALTER USER ... WITH LOGIN statement where a same-named login exists, or an explicit comment explaining why it can’t auto-fix that one. The EXEC sp_executesql lines that would actually apply the fix are commented out by default; nothing changes on the instance from running this script alone.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Generate the orphaned-user remap script for review:
.\run.ps1 Fix-OrphanedUsers

# To run against a remote sql server:
.\run.ps1 Fix-OrphanedUsers -ServerInstance SQLSERVER01

This script lives in the repo at:

Example Output

The script returns a single ddl column containing the full generated script as text. Real output against this lab instance:

-- ================================================================
-- Orphaned User Fix Script
-- Target  : PWSQL01
-- Generated: 2026-07-31 07:23:47
-- Review before executing. Each line maps a database user to a login
-- by name - verify the name match is correct first.
-- ================================================================

-- DemoDatabase: 1 orphan(s)

USE [DemoDatabase];
-- Cannot auto-fix: no login named [DemoSeverityUser] found. Create the login first or map manually.
GO

Real output captured 2026-07-31 against .. Reformat/trim as needed; screenshot preferred for the live post.

Understanding the Results

This is the same DemoSeverityUser orphan documented on the live Get Orphaned Users post, and the output above shows the script’s honest second branch rather than the simple case. It found the orphan, but because no login named DemoSeverityUser exists on this instance, it didn’t generate an ALTER USER statement for it. Instead it left a comment explaining exactly why, so nobody mistakes silence for “nothing to fix”.

The two possible outcomes per orphaned user:

  • A same-named login exists. The script generates ALTER USER [name] WITH LOGIN = [name];, ready to review and run.
  • No matching login exists, as in the real output above. The script leaves a comment instead of guessing, because remapping to the wrong login would be worse than leaving the user orphaned. You’ll need to create the login first (if it should exist) or map the user to a differently-named login by hand.

If the script finds zero orphans across every database, the generated output says so explicitly (-- No orphaned users found...) rather than returning nothing, so an empty-looking result is never ambiguous between “clean” and “the script didn’t run”.

Best Practices

  • Always review the generated script before running any of it. The name-matching assumption (login name equals user name) is the common case, not a guarantee.
  • Keep the EXEC sp_executesql lines commented out until you’ve reviewed the output; the script is a generator, not an auto-fixer, by design.
  • Run it on the TARGET server after logins have been created, not before. An orphan with no matching login is a signal to go create the login, not a script bug.
  • Re-run after creating any missing logins to confirm the remaining output is genuinely empty.

Related Scripts

You may also find these scripts useful:

Summary

Fix-OrphanedUsers turns “you have orphaned users” into a reviewable script, not a chore done user by user across every restored database. It generates the fix where a fix is safe to generate, and says exactly why it can’t where it isn’t, the same honest distinction this post’s own real output demonstrates.

Pair it with Get Orphaned Users for detection and this script for the remediation script, and run both as a standing step after any migration or restore where login SIDs weren’t guaranteed to carry over.

Blog Categories

Recent Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *