RDS/SQL Server User Management

RDS/SQL Server User Management

(1) When your destination MS SQL Server is RDS and you do not want to use the root account, provided by AWS, for replication purposes, we recommend the following script (different for source and destination servers) to be used to create a new user to be used for replications:

(1.1) Execute below script under the root account of the SOURCE RDS/SQL Server. Use it for replication:

USE master;
GO

CREATE LOGIN [replicationUserSource]
WITH PASSWORD = N'myPassword',
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;
GO

GRANT ALTER ANY CONNECTION TO [replicationUserSource] WITH GRANT OPTION;
GRANT ALTER ANY LINKED SERVER TO [replicationUserSource] WITH GRANT OPTION;
GRANT ALTER ANY LOGIN TO [replicationUserSource] WITH GRANT OPTION;
GRANT ALTER SERVER STATE TO [replicationUserSource] WITH GRANT OPTION;
GRANT ALTER TRACE TO [replicationUserSource] WITH GRANT OPTION;
GRANT CREATE ANY DATABASE TO [replicationUserSource] WITH GRANT OPTION;
GRANT VIEW ANY DATABASE TO [replicationUserSource] WITH GRANT OPTION;
GRANT VIEW ANY DEFINITION TO [replicationUserSource] WITH GRANT OPTION;
GRANT VIEW SERVER STATE TO [replicationUserSource] WITH GRANT OPTION;

EXEC SP_MSFOREACHDB '
DECLARE @name VARCHAR(200)
SET @name=''[?]''
IF DB_ID(@name) > 5
BEGIN
USE [?]
CREATE USER replicationUserSource FOR LOGIN replicationUserSource;
EXEC sp_addrolemember ''db_owner'',''replicationUserSource''
END'

(1.2) Execute below script under the root account of the DESTINATION RDS/SQL Server. Use it for replication:

USE master;
GO

CREATE LOGIN [replicationUserDest]
WITH PASSWORD = N'myPassword',
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;
GO

GRANT ALTER ANY CONNECTION TO [replicationUserDest] WITH GRANT OPTION;
GRANT ALTER ANY LINKED SERVER TO [replicationUserDest] WITH GRANT OPTION;
GRANT ALTER ANY LOGIN TO [replicationUserDest] WITH GRANT OPTION;
GRANT ALTER SERVER STATE TO [replicationUserDest] WITH GRANT OPTION;
GRANT ALTER TRACE TO [replicationUserDest] WITH GRANT OPTION
GRANT CREATE ANY DATABASE TO [replicationUserDest] WITH GRANT OPTION;
GRANT VIEW ANY DATABASE TO [replicationUserDest] WITH GRANT OPTION;
GRANT VIEW ANY DEFINITION TO [replicationUserDest] WITH GRANT OPTION;
GRANT VIEW SERVER STATE TO [replicationUserDest] WITH GRANT OPTION;

DB replicas must be used in read-only mode. We recommend using below script to create a read-only user:

(2) Execute below script under the account used for replication (the replicationUser in (1.2) above). It will create a new READ-ONLY user that has access to all databases under the new account created in (1.2):

USE master;
GO

CREATE LOGIN readonlyUserDest
WITH PASSWORD = N'myPassword',
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;
GO

EXEC SP_MSFOREACHDB '
DECLARE @name VARCHAR(200)
SET @name=''[?]''
IF DB_ID(@name) > 5
BEGIN
USE [?]
CREATE USER readonlyUserDest FOR LOGIN readonlyUserDest;
EXEC sp_addrolemember ''db_datareader'',''readonlyUserDest''
END'