.svg)
Article Summary:
Detect SID mismatches after migration, remap users safely and prevent orphaned SQL Server users from returning.
A database user and a server login can have the same name and still fail to map. SQL Server links them through a security identifier, or SID. When a database is restored to another instance and the login is recreated with a different SID, the database user becomes orphaned. Repair the mapping only after confirming the intended identity and access.
Why orphaned users appear
SQL-authenticated logins receive a SID when they are created. The corresponding database user stores that SID. Restoring the database brings the database SID with it, but manually creating a same-named login on the destination produces a new SID.
The symptom can be a login that connects to the instance but cannot access the database, or a user shown without a matching login. Contained database users follow a different authentication model and should not be handled as traditional orphans.
Detect the mismatch
Use the method in Microsoft's orphaned-user troubleshooting guidance to compare database and server principals. Exclude system users and identities that are intentionally contained. Run the check in every migrated database, not only the one that generated a ticket.
- User name and type
- Database SID
- Expected server login
- Server SID
- Roles and explicit permissions
Map the right identities
When the correct login exists, use ALTER USER with the LOGIN option to remap the database user. Do not map by name alone in an environment with similarly named service, test or supplier accounts. Confirm ownership with the application or security owner.
If the login is missing, decide whether to recreate it with the original SID and controlled password-hash migration, use a Windows or Microsoft Entra identity, or move to a contained user where supported.
Avoid deprecated fixes
Microsoft marks sp_change_users_login as deprecated. Current runbooks should use ALTER USER. Remove old scripts from migration packs so an emergency does not revive an unsupported habit.
Make security part of migration acceptance
Script and protect server-level principals before cutover, reconcile SIDs after restore and test the application using its real execution identity. Review database owners, SQL Agent proxies, credentials and linked-server mappings at the same time because they do not live entirely inside the user database.
- Export logins securely
- Preserve SIDs where appropriate
- Reconcile users after restore
- Test application and job identities
- Remove obsolete principals after approval
Talk to a database specialist
A change to support, licensing, cloud architecture or a production database should be based on evidence from your own estate. Pro DBA can assess the current position, identify the material risks and help you plan the next step. Contact the Pro DBA team to discuss your environment.
