What Are Orphaned Users and Why Do They Cause Problems?

When you move a database from one SQL Server instance to another, the database users travel with it but the server-level logins do not. This mismatch creates what SQL Server calls orphaned users - database users that have no corresponding login on the new server, or whose login exists but no longer matches by SID (Security Identifier). The result is broken access, failed application connections, and frustrated users who can't get into systems they should be able to reach.

Fixing orphaned users on SQL Server requires re-linking database users to server logins, either by matching SIDs or by re-associating them using the sp_change_users_login stored procedure. The approach you take depends on whether you're dealing with Windows logins or SQL logins, and whether the database is read-write or part of a log shipping or availability group configuration.

This is one of the most common issues we see after database migrations, restores, and disaster recovery failovers. It's also one of the easiest to overlook until something breaks in production.

Why Windows Logins and SQL Logins Behave Differently

Understanding why orphaned users happen requires a quick look at how SQL Server handles identity.

Windows logins are tied to Active Directory. Their SID is defined externally by AD, which means the SID is consistent across servers in the same domain. Recreating a Windows login on the new server with the same domain account automatically re-establishes the link to the database user. No orphan, no problem.

SQL logins are different. SQL Server generates a unique SID for each SQL login at creation time. When a database is moved to a new server, the database user retains the original SID from the source server. If you create a fresh SQL login on the destination server, SQL Server assigns it a new SID. The two no longer match, and the user becomes orphaned.

This distinction matters because it determines which fix you should apply.

What Is the Best Way to Fix Orphaned Users?

There are two main strategies, and the right choice depends on your environment.

Strategy 1: Export and import the SQL login (preferred)

The cleanest fix is to export the SQL login from the source server, preserving the original SID, and import it on the destination server. Microsoft provides a script for this in their Knowledge Base article KB918992, which generates CREATE LOGIN statements that include the hashed password and the original SID.

When the SIDs match, SQL Server automatically recognises the relationship between the login and the database user. No re-linking required. This is also the only viable approach when the destination database is read-only, such as a warm standby involved in log shipping or a secondary replica in an Always On Availability Group. You cannot run repair scripts against a read-only database.

Strategy 2: Create a new login and re-associate it

If exporting the original login isn't possible (for example, you don't have access to the source server, or the login was deleted), you can create a new SQL login on the destination server and then re-link it to the existing database user. This is where sp_change_users_login comes in.

How to Identify Orphaned Users

Before you fix anything, you need to know what you're dealing with. Run this against the affected database to report on any orphaned users:

EXEC sp_change_users_login 'Report';

This returns a list of database users that have no matching login SID on the current server. If the result set is empty, you have no orphaned users. If it returns rows, each one needs attention.

Run this in the context of the specific database you're investigating, not master. A common mistake is running it without switching databases first and then wondering why no results appear.

How to Fix Orphaned Users in SQL Server

Once you've identified the orphaned users, you have two repair options.

Option 1: Auto Fix

The Auto_Fix option attempts to match the database user to an existing login with the same name. If no matching login exists, it creates one.

EXEC sp_change_users_login 'Auto_Fix', 'username';

Replace username with the name of the orphaned database user. If a login with that name already exists on the server, SQL Server re-links the two by updating the SID in the database. Quick and simple, but use it with care. If the login name on the new server belongs to a different account than intended, you'll grant the wrong person access.

You can also pass a password, which causes Auto_Fix to create a new login if one doesn't already exist:

EXEC sp_change_users_login 'Auto_Fix', 'username', 'login', 'password';

Option 2: Manual re-association (recommended)

For more control, use Update_One to explicitly map a specific database user to a specific login:

EXEC sp_change_users_login 'Update_One', 'dbUser', 'sqlLogin';

Here, dbUser is the name of the orphaned database user and sqlLogin is the name of the server-level login you want to link it to. This approach is safer in environments where login names may not match user names, or where you're remapping access as part of a broader migration cleanup.

A Note on sp_change_users_login Deprecation

Microsoft deprecated sp_change_users_login in SQL Server 2008 and has flagged it for removal in a future version. It still works as of SQL Server 2022, but the recommended replacement is ALTER USER.

For modern SQL Server environments, the equivalent manual fix looks like this:

ALTER USER dbUser WITH LOGIN = sqlLogin;

This achieves the same result as Update_One but uses supported syntax. If you're writing scripts for long-term use or running SQL Server 2016 and later, favour ALTER USER over sp_change_users_login.

What to Watch Out For

A few practical considerations based on real-world experience:

  • Don't run repair scripts on read-only databases. If the database is a log shipping secondary or an AG secondary in read-only mode, you must fix the login at the server level by importing the original SID. You cannot modify the database user directly.
  • Check all databases, not just the obvious one. After a server migration, multiple databases may have orphaned users. Run the report query against each user database, or script it to run across all databases using sp_MSforeachdb or a cursor.
  • Verify application connectivity after fixing. Don't assume the fix worked. Test the application connection explicitly and check SQL Server error logs for any residual login failures.
  • Document the login mappings before migration. Running the report query on the source server before the move gives you a clean reference of which users exist and which logins they map to. This makes post-migration remediation significantly faster.

Key Takeaways

  • Orphaned users occur when a database is moved to a new SQL Server and the database users lose their link to server-level logins, most commonly with SQL logins due to SID mismatches.
  • The preferred fix is to export the original SQL login with its SID intact and import it on the new server, preserving the relationship automatically.
  • When that's not possible, use ALTER USER dbUser WITH LOGIN = sqlLogin (or sp_change_users_login 'Update_One' on older versions) to manually re-link users to logins.
  • Always run EXEC sp_change_users_login 'Report' first to identify which users are affected before attempting any fixes.
  • sp_change_users_login is deprecated. Use ALTER USER in modern SQL Server environments.

Orphaned users are a routine part of database migration and recovery work, but they can cause serious access problems if they're not caught early. A structured pre-migration checklist and a post-restore validation process eliminates most of the pain.

If you're managing regular database migrations, restores, or disaster recovery failovers, DBA Services can help you build a repeatable process that catches these issues before they reach production. Our SQL Server health checks include login and user mapping validation as a standard item. Get in touch to find out how we can support your environment.