CLOUDBASIX DB User Management

Documentation

We recommend the following scripts (different for source and replica servers; different for Amazon RDS SQL Server, AzureSQL DB, AzureSQL Managed Instance, Google CloudSQL, Snowflake, Redshift) 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:

Important /applicable to CDC (Change Data Capture) based jobs only/:
For a database(s) not enabled for CDC yet, if must use the limited permission user, created with the script below, CDC on database level only must be enabled manually, under a sysadmin user, using one of the below scripts.

AWS RDS for SQL Server:exec msdb.dbo.rds_cdc_enable_db 'database_name'

SQL Server (non-managed),
Azure Managed Instance SQL Server,
AzureSQL: USE database_name; exec sys.sp_cdc_enable_db;

Google CloudSQL for SQL Server: exec msdb.dbo.gcloudsql_cdc_enable_db ‘database_name’

Note: Do not have to enable CDC manually, if can use a sysadmin account to initiate a replication job. It can be changed to a limited permission user (replicationUserSource in below applicable script) after the initial seeding, in the continuous replication job settings.

To create a limited permission user, with sufficient permissions to source data, except to enable CDC for databases not already enabled for CDC (see above; applicable only to cases where must select CDC as a tracking of changes method), use one of the scripts below:

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

Amazon RDS SQL Server, Azure SQL Managed (not applicable to AzureSQL DB & Google CloudSQL); 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):

-- Execute against master (note: switching of databases with USE master; is not supported in SSMS against AzureSQL)

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

USE {database_name}
GO
EXEC sp_addrolemember 'db_owner', 'replicationUserSource';
GO

Google CloudSQL for SQL Server:

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

GRANT VIEW ANY DATABASE TO [replicationUserSource] AS CustomerDbRootRole;
GRANT VIEW ANY DEFINITION TO [replicationUserSource] AS CustomerDbRootRole;
GRANT VIEW SERVER STATE TO [replicationUserSource] AS CustomerDbRootRole;

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'

(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'

(3) Applicable to CLOUDBASIX for Snowflake:

Creating a Snowflake user with minimum required permissions, along with DWH, Database & Schema:

CREATE ROLE MY_CUSTOM_ROLE;
GRANT ROLE MY_CUSTOM_ROLE TO ROLE PUBLIC;

CREATE WAREHOUSE IDENTIFIER('MY_WH') COMMENT = '' WAREHOUSE_SIZE = 'X-Small' AUTO_RESUME = true AUTO_SUSPEND = 600;
CREATE USER MY_CUSTOM_ROLE_USER PASSWORD='xxxxxxxxxx' DEFAULT_ROLE = "MY_CUSTOM_ROLE" DEFAULT_WAREHOUSE = 'MY_WH' MUST_CHANGE_PASSWORD = FALSE;
GRANT MODIFY, MONITOR, OPERATE, USAGE ON WAREHOUSE MY_WH TO ROLE MY_CUSTOM_ROLE;
GRANT ROLE MY_CUSTOM_ROLE TO USER MY_CUSTOM_ROLE_USER;

--GRANT CREATE DATABASE ON ACCOUNT TO ROLE MY_CUSTOM_ROLE;
-- OR
--GRANT ALL PRIVILEGES ON ACCOUNT TO ROLE MY_CUSTOM_ROLE;
-- OR create a database below
USE WAREHOUSE MY_WH;
CREATE DATABASE MY_DB;

GRANT MONITOR ON DATABASE IDENTIFIER('MY_DB') TO ROLE IDENTIFIER('MY_CUSTOM_ROLE');
GRANT REFERENCE_USAGE ON DATABASE IDENTIFIER('MY_DB') TO ROLE IDENTIFIER('MY_CUSTOM_ROLE');
GRANT MODIFY ON DATABASE IDENTIFIER('MY_DB') TO ROLE IDENTIFIER('MY_CUSTOM_ROLE')
GRANT CREATE SCHEMA ON DATABASE IDENTIFIER('MY_DB') TO ROLE IDENTIFIER('MY_CUSTOM_ROLE');
GRANT USAGE ON DATABASE IDENTIFIER('MY_DB') TO ROLE IDENTIFIER('MY_CUSTOM_ROLE');
GRANT CREATE STAGE ON FUTURE SCHEMAS IN DATABASE IDENTIFIER('MY_DB') TO ROLE IDENTIFIER('MY_CUSTOM_ROLE');
GRANT ALL PRIVILEGES ON SCHEMA PUBLIC TO ROLE IDENTIFIER('MY_CUSTOM_ROLE');

USE DATABASE MY_DB;
CREATE SCHEMA MY_SCHEMA;
GRANT MONITOR, MODIFY, USAGE ON SCHEMA IDENTIFIER('MY_DB.MY_SCHEMA') TO ROLE IDENTIFIER('MY_CUSTOM_ROLE');
MY_DB.MY_SCHEMA TO ROLE MY_CUSTOM_ROLE;

For more information about the granted permission, visit:

https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html