RDS/EC2 SQL Server/Azure SQL User Management

Documentation

Documentation Index

CloudBasic:

 

API Documentation:

RDS/EC2 SQL Server/Azure SQL User Management

(1) Use below script to create a login with the minimum required permissions on the source side.
When your replica MS SQL Server is on Amazon 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 replica servers) to be used to create a new replication login:

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

Non-Azure SQL (incl. Amazon RDS):

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(500)
SET @name=''?''
IF DB_ID(@name) > 5
BEGIN
USE [?]
CREATE USER replicationUserSource FOR LOGIN replicationUserSource;
EXEC sp_addrolemember ''db_owner'',''replicationUserSource''
END'

Azure SQL:

  • Connect to the master database under the Azure SQL root account and execute below script (the password must include a special character and a number) to create a login:
CREATE LOGIN [replicationUserSource] WITH PASSWORD = N'myPassword|123'; GO
  • Connect to the respective source database and execute below script to create the user:
CREATE USER [replicationUserSource] FOR LOGIN [replicationUserSource] WITH DEFAULT_SCHEMA = dbo GO
  • Connect to the master database and execute below script to create the user:
CREATE USER [replicationUserSource] FOR LOGIN [replicationUserSource] WITH DEFAULT_SCHEMA = dbo GO
  • Add the user to the database owner role (execute against the respective source database):
EXEC sp_addrolemember 'db_owner','replicationUserSource' GO


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

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(500)
SET @name=''?''
IF DB_ID(@name) > 5
BEGIN
USE [?]
CREATE USER readonlyUserDest FOR LOGIN readonlyUserDest;
EXEC sp_addrolemember ''db_datareader'',''readonlyUserDest''
END'