How to synchronize the database users between instances

Owned by Agat Support
Feb 21, 2021
1 min read

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'

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

USE [master] --Create the login CREATE LOGIN <UserName> WITH PASSWORD = '<yourPassword>', sid=<UserSID>

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