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
Comments