How to synchronize the database users between instances

Aim

In certain cases you will be required to synchronize the users between multiple SQL servers instances:

1. New deployment of an SQL always on instance

2. Configuration of SQL always on

3. Migrating the database into a new SQL instance

Instructions

Follow these steps to restore the users:

1. Run the following query to get the orphaned users SID and login

   USE [DB_Name] -- Get the user's SID EXEC SP_CHANGE_USERS_LOGIN 'REPORT'

Open

1. Copy the UserName and UserSID values and paste them in the appropriate field in the following command:

1. Connect to the database with that user and run a SELECT query on one of that tables to validate permissions.