How to Synchronize Logins on Secondary Server

How to Synchronize Logins on Secondary Server

I came across the following situation.
Server A has a daily snapshot of a database (Database1) going to Server B (SnapDatabase1)

When a new SQL login is created on Server A – this login does not work on Server B. Users get a login failed error.

This situation usually applies to the following HA solutions:

  • AlwaysOn Availability Groups
    -Database Mirroring
  • Log Shipping (when reading from stand-by database)

For this example we will assume sql login testuser1

From Server A

SELECT name, sid FROM sys.sysusers WHERE name = 'testuser1' 
GO 
USE MASTER 
GO 
SELECT name, sid FROM sys.sql_logins WHERE name = 'testuser1' 
GO

As you can see the sid’s match on Server A

Now lets get the sid for the same login from Server B

As you can see the sid for Testuser1 on Server B does not match the sid for Testuser1 on Server A.

In order to fix this, we will delete the Testuser1 login from Server B.

Next, we will create the login using the sql script below:

CREATE Login Testuser1 WITH password = 'Test@123', SID = 0x96B7F79D2585E34FAB5E653DF2EC4AE3

We will replace the sid with the same sid that is used with the login Testuser1 on Server A.

Next, we run check the sid of Testuser1 on Server B:

CREATE Login Testuser1 WITH password = 'Test@123', SID = SELECT name, sid FROM sys.sysusers WHERE name = 'testuser1' 
GO 
USE MASTER 
GO 
SELECT name, sid FROM sys.sql_logins WHERE name = 'testuser1' 
GO

We get this:

As you can see, the sid for Testuser1 on Server B now matches the sid for Testuser1 on Server A.

Hope this helps resolve any issues you might have with sql login sid.