All you need is to create the same login on the second server preserving it’s sid, and you can do this first by copying the login’s sid from sys.server_principals and then by creating the same login using with sid like this:

create login my_login with password = '*****', sid = 0x6E94CADC0A6EEC439B620643CDDB5C71 -- put here your login's sid 

Example:

On Primary Database:

USE master
GO

select * from  sys.server_principals

-- name = user01
-- sid = 0xA72A66203E6C3743B9500EE520BD1234

On Read Only Replica Database:

USE master
GO

create login user01 with password = 'test123', sid = 0xA72A66203E6C3743B9500EE520BD1234

Sources:

https://dba.stackexchange.com/questions/174946/creating-a-sql-login-and-user-on-an-alwayson-replica

Last modified: February 23, 2021

Author

Comments

Write a Reply or Comment