SQL Server Find Orphan Users Script

calendar Apr 17, 2026 / · 7 min read · sql server security orphan users sys.database_principals sys.server_principals sp_change_users_login database administration sql scripts security audit login management  ·
Share on: linkedin copy

Orphaned database users are a common problem after database restores, server migrations, or login deletions. A database user becomes orphaned when its SID does not match any server-level login SID. This script queries sys.database_principals and sys.server_principals to find every orphaned user in a database, and shows how to fix them.

Purpose and Overview

When you restore a database to a new server, or when a Windows account or SQL login is dropped at the server level, the corresponding database user record remains in the database. That user is now orphaned — it exists in sys.database_principals but has no matching entry in sys.server_principals by SID.

Orphaned users cannot log in. They can also cause unexpected permission errors and complicate security audits. This script helps you detect every orphaned user across a database and provides two methods to fix them: ALTER USER ... WITH LOGIN for SQL Server 2005 and later, and sp_change_users_login for older compatibility.

Code Breakdown

 1-- Find orphaned database users (SID does not match any server login)
 2SELECT
 3    DB_NAME()                   AS DatabaseName,
 4    dp.name                     AS UserName,
 5    dp.type_desc                AS UserType,
 6    dp.sid                      AS UserSID,
 7    dp.create_date              AS CreateDate
 8FROM sys.database_principals AS dp
 9WHERE dp.type IN ('S', 'U', 'G')          -- SQL users, Windows users, Windows groups
10  AND dp.sid IS NOT NULL
11  AND dp.sid NOT IN (
12        SELECT sid
13        FROM sys.server_principals
14        WHERE sid IS NOT NULL
15      )
16  AND dp.name NOT IN (
17        'dbo',
18        'guest',
19        'INFORMATION_SCHEMA',
20        'sys'
21      )
22  AND dp.principal_id > 4                  -- skip built-in principals
23ORDER BY dp.name;
24
25-- ------------------------------------------------------------
26-- Fix option 1: ALTER USER (SQL Server 2005+, recommended)
27-- Replace 'OrphanedUser' and 'ExistingLogin' with actual names.
28-- ------------------------------------------------------------
29-- ALTER USER [OrphanedUser] WITH LOGIN = [ExistingLogin];
30
31-- ------------------------------------------------------------
32-- Fix option 2: sp_change_users_login (legacy, deprecated in
33-- newer SQL Server versions but still works)
34-- Replace 'OrphanedUser' and 'ExistingLogin' with actual names.
35-- ------------------------------------------------------------
36-- EXEC sp_change_users_login 'Update_One', 'OrphanedUser', 'ExistingLogin';
37
38-- ------------------------------------------------------------
39-- Fix option 3: sp_change_users_login auto-fix
40-- Automatically remaps a SQL user to a login with the same name.
41-- ------------------------------------------------------------
42-- EXEC sp_change_users_login 'Auto_Fix', 'OrphanedUser';

Filtering by user type

The WHERE dp.type IN ('S', 'U', 'G') clause limits results to three user types:

  • S — SQL Server authenticated users
  • U — Windows authenticated individual users
  • G — Windows group-based users

Built-in pseudo-users such as certificate-mapped users (C) and asymmetric key users (K) are excluded because they do not map to server logins in the same way.

SID comparison against sys.server_principals

The subquery SELECT sid FROM sys.server_principals WHERE sid IS NOT NULL collects every SID that belongs to a valid server-level login. The outer query returns only users whose SID is absent from that list — these are the orphaned users.

sys.server_principals is a server-scoped catalog view and is always readable from any database context, making the join reliable without needing cross-database references.

Excluding built-in accounts

Two exclusions work together:

  • dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys') — removes named system users that exist in every database by design.
  • dp.principal_id > 4 — removes low-numbered built-in principals regardless of name, covering sa (principal_id 1) and other fixed system accounts.

Fix option 1: ALTER USER WITH LOGIN

ALTER USER [OrphanedUser] WITH LOGIN = [ExistingLogin] is the current recommended method. It updates the SID of the database user to match the target login, restoring connectivity. The login must already exist at the server level.

Fix option 2 and 3: sp_change_users_login

sp_change_users_login is a legacy stored procedure that performs the same SID update. It is marked deprecated in SQL Server documentation but remains functional. Use Update_One mode to map a specific user to a specific login. Use Auto_Fix mode to automatically link a user to a login that shares the same name, which also creates a new login if one does not exist (with a password you supply as a fourth argument).

Key Benefits and Use Cases

  • Detects all orphaned users in the current database with a single query
  • Covers SQL users, Windows users, and Windows group users
  • Excludes every built-in system account to avoid false positives
  • Displays the SID for comparison or audit trail purposes
  • Provides three practical fix methods to restore login access
  • Runs on SQL Server 2005 and later with no special permissions beyond VIEW DATABASE STATE
  • Useful immediately after a database restore, server migration, or login cleanup
  • Supports security compliance reviews that require all database users to have valid server logins

Performance Considerations

Both sys.database_principals and sys.server_principals are in-memory catalog views backed by system metadata. Queries against them are fast and do not require table scans of user data. There is no measurable performance impact from running this script, even on busy servers.

The NOT IN subquery against sys.server_principals is evaluated once and the result set is small (typically fewer than a few hundred rows). If you prefer, you can rewrite it as a LEFT JOIN ... WHERE sp.sid IS NULL pattern, which is functionally equivalent and may be easier to read in larger scripts.

 1-- Alternative LEFT JOIN form
 2SELECT
 3    DB_NAME()       AS DatabaseName,
 4    dp.name         AS UserName,
 5    dp.type_desc    AS UserType,
 6    dp.sid          AS UserSID,
 7    dp.create_date  AS CreateDate
 8FROM sys.database_principals AS dp
 9LEFT JOIN sys.server_principals AS sp
10    ON dp.sid = sp.sid
11WHERE dp.type IN ('S', 'U', 'G')
12  AND dp.sid IS NOT NULL
13  AND sp.sid IS NULL
14  AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
15  AND dp.principal_id > 4
16ORDER BY dp.name;

Practical Tips

Run after every restore. Add this script to your post-restore checklist. A restored database almost always has orphaned users unless you scripted the logins ahead of time with their original SIDs.

Use ALTER USER, not sp_change_users_login, for new work. Microsoft has marked sp_change_users_login as deprecated. It still works in SQL Server 2022 but may be removed in a future version.

Script logins with SIDs before migrating. To avoid orphans entirely during a migration, use sp_help_revlogin or script logins with the HASHED password and original SID. This preserves SID alignment across servers.

Check all user databases. This script runs in the context of the current database. To check all databases, wrap it in sp_MSforeachdb or a cursor that changes database context. Run the query once per database or use dynamic SQL.

 1-- Example: run across all user databases
 2EXEC sp_MSforeachdb '
 3USE [?];
 4SELECT
 5    ''?''                   AS DatabaseName,
 6    dp.name                 AS UserName,
 7    dp.type_desc            AS UserType,
 8    dp.sid                  AS UserSID,
 9    dp.create_date          AS CreateDate
10FROM sys.database_principals AS dp
11WHERE dp.type IN (''S'', ''U'', ''G'')
12  AND dp.sid IS NOT NULL
13  AND dp.sid NOT IN (
14        SELECT sid FROM sys.server_principals WHERE sid IS NOT NULL
15      )
16  AND dp.name NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
17  AND dp.principal_id > 4
18ORDER BY dp.name;
19';

Verify the fix. After running ALTER USER or sp_change_users_login, re-run the detection query to confirm the user no longer appears in the results.

Windows accounts. If the orphaned user is a Windows account that no longer exists in Active Directory, you cannot remap it to a valid login. In that case, consider dropping the database user if it is no longer needed, after confirming no objects are owned by it.

Conclusion

Orphaned database users are a routine side effect of database restores and server migrations. Catching them early prevents access failures and keeps your security posture clean. This script gives you a reliable, low-overhead way to detect every orphaned user using sys.database_principals and sys.server_principals, and provides the correct T-SQL commands to fix each one. Add it to your post-restore runbook and run it as part of any security audit.

References

Posts in this series