1. Microservices Architecture Documentation
  2. Prepare
  3. Prepare Databases
  4. SQL Server
  5. Prepare Database Users and Privileges for SQL Server

Prepare Database Users and Privileges for SQL Server

The following database users and privileges are required for Oracle GoldenGate to capture from and apply to a SQL Server database.

Database User for SQL Server

Oracle GoldenGate processes require a database user in order to capture from and apply data to a SQL Server database and it is recommended to create a dedicated database user to be used exclusively by GoldenGate processes.

Oracle GoldenGate for SQL Server supports SQL Server authentication for all of its certified platforms .

To use SQL Server authentication, create a dedicated SQL Server login for Extract and Replicat and assign the privileges listed below.

Required Database Priviliges

SQL Server and Azure SQL Managed Instance

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to capture from and apply to a SQL Server or Azure SQL Managed Instance database.

  1. Create a dedicated login for Oracle GoldenGate for SQL Server or Azure SQL Managed Instance.

  2. Add the login as a user to the msdb database and to the source or target database.

  3. Create a schema in the source or target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.

  4. Enable the following privileges and permissions for the Oracle GoldenGate user based on whether the user is for an Extract, or for a Replicat.

Table 4-4 Privileges and Permissions for Oracle GoldenGate User

Privilege Extract Replicat Syntax

msdb Database Roles and Privileges

SQLAgentReaderRole

Yes

No

ALTER ROLE SQLAgentReaderRole ADD MEMBER gguser;

SQLAgentUserRole

Inherited

Yes

ALTER ROLE SQLAgentUserRole ADD MEMBER gguser;

User Database Roles and Privileges

SYSADMIN

Yes

No

Required for a one time change to enable database level Change Data Capture (CDC) if not already enabled and can be revoked once TRANDATA has been enabled.

ALTER SERVER ROLE sysadmin ADD MEMBER gguser;

Database Administrators with sysadmin credentials can manually enable the database for CDC using the following, which would negate the need for the Extract user to have this privilege:

EXEC msdb.sys.sp_cdc_enable_db ‘source_database’

DBOWNER

Yes

Yes

 ALTER ROLE db_owner ADD MEMBER gguser;

Amazon RDS for SQL Server

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to capture from and apply to an Amazon RDS for SQL Server database:

  1. Create a dedicated login for Oracle GoldenGate for Amazon RDS for SQL Server.

  2. Add the login as a user to the msdb database and to the source or target database.

  3. Create a schema in the source or target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.

  4. Enable the following privileges and permissions for the Oracle GoldenGate user based on whether the user is for an Extract, or for a Replicat.

Table 4-5 Privileges and Permissions for Oracle GoldenGate User

Privilege Extract Replicat Syntax

msdb Database Roles and Privileges

EXECUTE ON rds_cdc_enable_db

Yes

No

GRANT EXECUTE ON msdb.dbo.rds_cdc_enable_db TO gguser;

Database administrators with master credentials can manually enable the database for Change Data Capture using the following command, which would negate the need for the Extract user to have this permission:

EXEC msdb.dbo.rds_cdc_enable_db ‘source_database’

SQLAgentOperatorRole

Yes

No

ALTER ROLE SQLAgentOperatorRole ADD MEMBER gguser;

SQLAgentUserRole

Inherited

Yes

ALTER ROLE SQLAgentUserRole ADD MEMBER gguser;

User Database Roles and Privileges

DBOWNER

Yes

Yes

ALTER ROLE db_owner ADD MEMBER gguser;

Azure SQL Database

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to apply to an Azure SQL Database:

  1. Create a dedicated login for Oracle GoldenGate for Azure SQL Database.

  2. Add the login as a user to the target database.

  3. Create a schema in the target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.

  4. Enable the following privileges and permissions for the Oracle GoldenGate user.

Table 4-6 Privileges and Permissions for Oracle GoldenGate User

Privilege Extract Replicat Syntax

User Database Roles and Privileges

DBOWNER

NA

Yes

ALTER ROLE db_owner ADD MEMBER gguser;

Google Cloud SQL for SQL Server

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to capture from and apply to a Google Cloud SQL for SQL Server database:

  1. Create a dedicated login for Oracle GoldenGate Google Cloud SQL for SQL Server. The user must be created from within the Users section of the Google Cloud dashboard for the database instance.

  2. Add the user to the source or target database.

  3. Create a schema in the source or target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.

  4. If the database is to be used as a source for an Extract, manually enable the database for Change Data Capture (CDC):

    EXEC msdb.dbo.gcloudsql_cdc_enable_db 'source_database';

  5. Enable the following privileges and permissions for the Oracle GoldenGate user based on whether the user is for an Extract, or for a Replicat.

Table 4-7 Privileges and Permissions for Oracle GoldenGate User

Privilege Extract Replicat Syntax

User Database Roles and Privileges

DBOWNER

Yes

Yes

ALTER ROLE db_owner ADD MEMBER gguser;