How to fix orphaned SQL Server users after migration

Owned by Bora Noyan, created
Last updated: 30-05-2023
1 min read


This how to article explains how to fix orphaned user after migration in SQL Server

 Instructions

Follow steps below:

  1. Connect to migrated SQL server and find migrated database.

  2. Right click and run a new query.

    EXEC sp_change_users_login 'Report'

  3. Get the results and run another query by replacing usernames in the result and execute.

    EXEC sp_change_users_login 'Auto_Fix', 'replace_user_name_in_the_result'

 

 

 

You can find your user name prefix from config file, here is an example below:

<dbdatabasename>epicentre_database</dbdatabasename>
<dbuserprefix>epicentre_prefix</dbuserprefix>

After migration, usernames in the result should be like:

  • epicentre_prefix_user

  • epicentre_prefix_dbo

  • epicentre_prefix_reader

 Related articles

Filter by label

There are no items with the selected labels at this time.