Orphaned Users After a Restore, and How to Fix Them

A restored database can contain orphaned users even when matching login names already exist on the destination. SQL Server maps these identities by SID, so the spelling alone does not establish the connection.

Understand What the Restore Brought Across

Database users travel with the database. Instance logins normally belong to the destination server and are not recreated by restoring a user database. That separation explains a common post-restore access failure.

A newly created SQL login usually receives a new SID. Reusing the old login name therefore does not guarantee a match with the restored user. The database can retain its permissions while the intended login cannot use them.

SELECT DB_NAME() AS database_name,
       ORIGINAL_LOGIN() AS original_login;
SELECT name, type_desc, authentication_type_desc, sid
FROM sys.database_principals
WHERE principal_id > 4
ORDER BY name;

Run the investigation in the restored database and under an account with adequate metadata visibility. A restricted catalog view can hide server principals. Treat visibility as a prerequisite for drawing conclusions.

Find the Instance-Authenticated Candidates

Start with users that depend on instance authentication and have no matching server SID. This deliberately excludes contained users that authenticate at database scope. Users created without a login are also not automatically broken.

SELECT dp.name AS user_name, dp.type_desc, dp.sid
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sid
WHERE sp.sid IS NULL
  AND dp.authentication_type_desc = 'INSTANCE'
  AND dp.principal_id > 4;

Interpret the result as a list to investigate, not a batch repair instruction. Confirm who owns each identity and whether access is still required. Restored historical accounts may be intentionally unused.

Windows identities and group-based access deserve their own review. A simple direct-name comparison does not describe every possible group membership path. Keep the scope of this SQL-login check clear.

Compare the Intended Pair Explicitly

Before remapping, identify the destination login that should receive the user’s existing database rights. Do not choose it merely because the names look similar. A mistaken mapping transfers access to the wrong principal.

DECLARE @User sysname = N'ApplicationUser';
DECLARE @Login sysname = N'ApplicationLogin';
SELECT dp.name AS database_user, dp.sid AS database_sid,
       sp.name AS server_login, sp.sid AS server_sid,
       sp.is_disabled
FROM sys.database_principals AS dp
CROSS JOIN sys.server_principals AS sp
WHERE dp.name = @User AND sp.name = @Login;

Replace both example names with the reviewed identities. No result can mean a missing name or insufficient visibility. Check disabled logins and connection failures separately from the database mapping.

Also review the restored user’s role memberships and explicit grants. Repairing the mapping preserves those rights. That is useful when the rights are intended and risky when nobody has reviewed them.

Repair the Mapping Without Rebuilding the User

When the approved login already exists, use ALTER USER WITH LOGIN in the restored database. This changes the mapping while retaining the database principal and its permissions. The login type must be compatible with the user.

-- Replace these reviewed names before executing in the restored database.
ALTER USER [ApplicationUser] WITH LOGIN = [ApplicationLogin];

Avoid dropping and recreating the user as a routine repair. That can disturb permissions, memberships, and ownership relationships. The deprecated sp_change_users_login procedure is not the preferred modern approach.

If the login is genuinely missing, use an approved login-transfer or creation process. Preserving the original SID can help when several databases depend on the same identity. Handle credentials through the established secure process, not a shared troubleshooting note.

Test Through the Real Connection

Repeat the SID comparison after the change, then connect using the application’s intended authentication path. Test the required operation in the restored database. An administrator’s successful SELECT does not verify the application account.

SELECT dp.name AS database_user, sp.name AS server_login,
       dp.authentication_type_desc
FROM sys.database_principals AS dp
JOIN sys.server_principals AS sp ON sp.sid = dp.sid
WHERE dp.name = N'ApplicationUser';

If access still fails, inspect the exact error and requested database. Default database settings, disabled logins, network access, and missing object permissions are separate possibilities. Do not keep remapping a SID that already matches.

Plan Portability Before the Next Restore

Include login handling in migration and disaster-recovery procedures. Availability replicas and replacement instances need consistent identity planning too. Rehearse access validation as part of the restore test.

Contained database users can reduce this dependency by authenticating at database scope. They also require suitable containment settings and a deliberate security model. Review connection targets and who can create users before adopting them.

Keep the chosen approach documented with the application owner. The objective is predictable access after movement, not simply making one error disappear. A restore is incomplete until the intended users can perform the intended work.

An orphaned user is not a missing name, it is a broken identity mapping.

This post was rewritten from scratch in September 2026. The original, published on 2007-08-02, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.