Create a user on AWS SQL Server Read replica

Question

As far as I know, AWS RDS does not sync the user creation on replica database. I created a user on the main database. However, the user has not been synced on the replica. I tried to create the user on replica. I received a read-only exception. The question is, how can we create a database user on AWS RDS SQL Server read replica?

Thanks for the answers.

Answer 1

From Working with read replicas for Microsoft SQL Server in Amazon RDS - Amazon Relational Database Service:

Synchronizing database users and objects with a SQL Server read replica

Any logins, custom server roles, SQL agent jobs, or other server-level objects that exist in the primary DB instance at the time of creating a read replica are expected to be present in the newly created read replica. However, any server-level objects that are created in the primary DB instance after the creation of the read replica will not be automatically replicated, and you must create them manually in the read replica.

The database users are automatically replicated from the primary DB instance to the read replica. As the read replica database is in read-only mode, the security identifier (SID) of the database user cannot be updated in the database. Therefore, when creating SQL logins in the read replica, it's essential to ensure that the SID of that login matches the SID of the corresponding SQL login in the primary DB instance. If you don't synchronize the SIDs of the SQL logins, they won't be able to access the database in the read replica. Windows Active Directory (AD) Authenticated Logins do not experience this issue because the SQL Server obtains the SID from the Active Directory.

It then provides details of how you can create a login on the Read Replica by specifying the SID from the from the Primary database, eg:

CREATE LOGIN TestLogin1 WITH PASSWORD = 'PASSWORD', SID=[FROM PRIMARY DB];