RDS/EC2 SQL Server/Azure SQL User Management

Documentation

Documentation Index

CloudBasic:

 

API Documentation:

Amazon RDS, Azure SQL, GoogleSQL Login/User Management

We recommend the following scripts (different for source and replica servers; different for Amazon RDS SQL Server, AzureSQL DB, AzureSQL Managed Instance, GoogleSQL) to be used to create new SQL Server replication login users (plus see recommended script to be used to create read-only reporting user, at the end):

(1) Source Login User: Use below script to create a login with the minimum required permissions on the source side.

(1.1) Execute below script under the source SQL Server sa/sysadmin, or the root account of RDS/Azure SQL:

Amazon RDS SQL Server, Azure SQL Managed (not applicable to AzureSQL DB); SQL Server on EC2, Azure VM, Google Cloud VM

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

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(500)
 SET @name=''?''
 IF DB_ID(@name) > 5
 BEGIN
 USE [?]
 CREATE USER replicationUserSource FOR LOGIN replicationUserSource;
 EXEC sp_addrolemember ''db_owner'',''replicationUserSource''
 EXEC sp_addrolemember ''db_denydatawriter'',''replicationUserSource'' -- Optional: deny write permissions to ensure replicationUserSource cannot be used by the application administrators to manually modify the source database in error
 END'

Azure SQL DB (not applicable to Azure SQL Managed Instance):

USE master;
GO
CREATE LOGIN [replicationUserSource]
WITH PASSWORD = N'myPassword'
GO
CREATE USER [replicationUserSource] FOR LOGIN [replicationUserSource] WITH DEFAULT_SCHEMA = dbo
GO
ALTER ROLE [dbmanager] ADD MEMBER replicationUserSource;
GO
ALTER ROLE [loginmanager] ADD MEMBER replicationUserSource;
GO

 

(1.2) Replica Login User: Execute below script under the replica SQL Server sa/sysadmin, or the admin account of RDS/Azure SQL:

Amazon RDS SQL Server, Azure SQL Managed (excluding AzureSQL DB); SQL Server on EC2, Azure VM, Google Cloud VM

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

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


AzureSQL DB (not applicable to Azure SQL Managed Instance):

USE master;
GO
CREATE LOGIN [replicationUserReplica]
WITH PASSWORD = N'myPassword'
GO
CREATE USER [replicationUserReplica] FOR LOGIN [replicationUserReplica] WITH DEFAULT_SCHEMA = dbo
GO
ALTER ROLE [dbmanager] ADD MEMBER replicationUserReplica;
GO
ALTER ROLE [loginmanager] ADD MEMBER replicationUserReplica;
GO

Above login is needed for the replication job to replicate data to the replica. However DB replicas must be accessed by reporting applications in read-only mode. We recommend using below script to create a read-only login with limited access, to prevent deny write, for the purpose of querying the replica database:

(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 readonlyUserReplica
WITH PASSWORD = N'myPassword',
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;
GO

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