For [**SQL Access**](/docs/dss6-requirements-source-and-target-requirements-sql-server-requirements-sql-server-as-source-capture-from-sql-server-using-sql-access) capture method, a user with a **sysadmin** privilege must create a special 'wrapper' SQL procedures called **sp_dss_dblog** (replaced with **sp_dss_dblog2** in `v6.1.5/5` and later) and **sp_dss_dbtable** so that the DSS can call the SQL Server's read-only function **fn_dump_dblog**. This must be done inside the SQL Server database's special database **msdb**, not the actual capture database. The SQL query to create these procedures is available in the file **dsscapsysadmin.sql** in directory **DSS_HOME\\sql\\sqlserver**. The DSS database **User** must then be allowed to execute this procedure. For this, the DSS database **User** must be added to the special **msdb** database and the following grants must be provided:

<div class="callout callout-important">

 use msdb;
 create user <em>username</em> for login <em>username</em>;
 grant execute on sp_dss_dblog to <em>username</em>;
 grant execute on sp_dss_dbtable to <em>username</em>;

• Grant special read-only privilege:

This step is needed once when DSS is installed for an Azure SQL Managed Instance. But if Always On AG is installed, then this step is needed on each Always On AG node.

A **sysadmin** user must grant the DSS user login a special read-only privilege in the **master** database.
```shell
use master;
grant view server state to <em>username</em>;
```

• Perform special SQL statements:
  When Activate Replication is performed, it may need to perform special SQL statements that would require sysadmin or db_owner privilege. One example is that it may need to create an Article on a replicated table to track its changes. In that case, Activate Replication will write the necessary SQL statements into a script file in directory DSS_CONFIG\hubs\hub_name\channels\channel_name\locs\location_name\initsql on the capture machine. An error message will be displayed, which will list the name of the script file and the necessary permission level. The first time when Activate Replication displays this message, a user with sysadmin privilege must perform these SQL statements on the capture database. Subsequently, these SQL statements can be performed by the DSS database User with db_owner privilege.

Minimal

The DSS database User is not granted or does not require sysadmin or db_owner roles at runtime.

Following are the configuration requirements that must be met/performed for this permission model:

• Create a special 'wrapper' SQL procedure:

This step is needed once when DSS is installed for an SQL Server instance. But if Always On AG is installed, then this step is needed on each Always On AG node.

For [**SQL Access**](/docs/dss6-requirements-source-and-target-requirements-sql-server-requirements-sql-server-as-source-capture-from-sql-server-using-sql-access) capture method, a user with a **sysadmin** privilege must create a special 'wrapper' SQL procedures called **sp_dss_dblog** (replaced with **sp_dss_dblog2** in `v6.1.5/5` and later) and **sp_dss_dbtable** so that the DSS can call the SQL Server's read-only function **fn_dump_dblog**. This must be done inside the SQL Server database's special database **msdb**, not the actual capture database. The SQL query to create these procedures is available in the file **dsscapsysadmin.sql** in the **DSS_HOME\\sql\\sqlserver** directory. The DSS database **User** must then be allowed to execute this procedure. For this, the DSS database **User** must be added to the special **msdb** database and the following grants must be provided:

<div class="callout callout-important">

For v6.1.5/5 and later, replace sp_dss_dblog with sp_dss_dblog2.

```shell
use msdb;
create user <em>username </em>for login <em>username</em>;
grant execute on sp_dss_dblog to <em>username</em>;
grant execute on sp_dss_dbtable to <em>username</em>;
```

• Grant special read-only privilege:

This step is needed once when DSS is installed for an SQL Server instance. But if Always On AG is installed, then this step is needed on each Always On AG node.

A **sysadmin** user must grant the DSS user login a special read-only privilege in the **master** database.
```shell
use <em>master</em>grant view server state to <em>username</em>
```

• Perform special SQL statements:
  When Activate Replication is performed, it may need to perform special SQL statements that would require sysadmin or db_owner privilege. One example is that it may need to create an Article on a replicated table to track its changes. In that case, Activate Replication will write the necessary SQL statements into a script file in the DSS_CONFIG\hubs\hub_name\channels\channel_name\locs\location_name\initsql directory on the capture machine. An error message will be displayed, which will list the name of the script file and the necessary permission level. The first time when Activate Replication displays this message, a user with sysadmin privilege must perform these SQL statements on the capture database. Subsequently, these SQL statements can be performed by a user with db_owner privilege.

• Create 'wrapper' SQL procedures in each capture database:

This step is needed once when each new source database is being set up.

A user with **db_owner** (or **sysadmin**) privilege must create 'wrapper' SQL procedures in each capture database so that DSS can call the SQL Server's read-only procedures **sp_helppublication**, **sp_helparticle** and **fn_dblog**. The SQL query to create these three read-only procedures is available in the file called **dsscapdbowner.sql** in directory **DSS_HOME\\dbms\\sqlserver**. The DSS database **User** must then be allowed to execute these procedures.

The following grants must be granted to the DSS database **User** inside each capture database:

<div class="callout callout-important">

Replace sp_dss_dblog with sp_dss_dblog2 for v6.1.5/5 and later.

 use <em>source_database</em>;
 grant execute on sp_dss_check_publication to <em>username</em>;
 grant execute on sp_dss_check_article to <em>username</em>;
 grant execute on sp_dss_dblog to <em>username</em>;
 grant execute on sp_dss_repldone to <em>username</em>;
 grant execute on sp_dss_repltrans to <em>username</em>;

• Grant read-only privilege:

This step is needed once when each new source database is being set up.

A user with **db_owner** (or **sysadmin**) privilege must grant the DSS database **User** a read-only privilege.

```shell
use <em>source_database</em>;
alter role db_datareader add member <em>username</em>;
```

Supplemental Logging

Supplemental logging is required to ensure table updates can be replicated correctly using SQL statements on a target database.

Transaction log files are generally used for database recovery and normally SQL Server only logs data that is needed for recovery. For example, full row image (all columns) are usually not written into the transaction log but only the fragments that are actually changed are written. For DSS to perform log-based capture, SQL Server needs to write full row images as well as write some additional information into the transaction log, which is referred to as "Supplemental Logging" in DSS.

SQL Server does not allow enabling supplemental logging directly. However, you can enable it by using the native feature of SQL Server called as 'CDC tables'. To do log-based capture, DSS requires a CDC table to be created for each source table.

When performing Activate Replication (dssactivate -ol), if the option Supplemental Logging is selected, DSS will check whether supplemental logging is enabled in the database. If supplemental logging is not enabled in the database, then DSS will create a CDC table.

Available option for SUPPLEMENTAL LOGGING is:

• Reuse any existing CDCTABs and ARTICLEs: Enable supplemental logging of updates by using the existing CDC table or replication article for the source table. If neither the CDC tables nor the replication article exists, then DSS will create a CDC table or replication article for the source table depending on the option (Prefer CDCTABs and Prefer ARTICLEs) selected along with this option.

Dropping the Source Database

Depending on the location property SUPPLEMENTAL LOGGING (Supplemental_Logging) defined, DSS will use some of SQL Server's own 'replication components' or it will use SQL Server's Change Data Capture (CDC) feature. Based on this, DSS may enable the 'publish' replication option for the source database. When this replication database option is enabled, attempts to drop the database will give an SQL Server error.

When performing Deactivate Replication (dssactivate -d -ol), if the option Supplemental Logging is selected/enabled, DSS will check if there are any replication articles remaining in the database. If none are remaining, it will also disable the 'publish' replication database option (in SQL Server database). The database can then be dropped.

Log Truncater

The location property LOG TRUNCATER (Log_Truncater) specifies who advances the SQL Server transaction log truncation point (truncates the log). For Azure SQL Managed Instance, only the CAP_JOB_RETAIN method is supported.

To capture changes from Azure SQL Managed Instance, the location property Log_Truncater must be set to CAP_JOB_RETAIN. In DSS UI, this property is automatically defined while creating the Azure SQL Managed Instance location.

CAP_JOB_RETAIN

This method should be used when capturing from an Azure SQL Managed Instance with the recovery mode set to Simple Recovery. The capture job moves the truncation point of the transaction log forward by calling the stored procedure sp_repldone at the end of each sub-cycle. Only part of the transaction log that has already been processed (captured) is marked for truncation (this is different from the CAP_JOB mode, where all records in the transaction log are marked for truncation, including those that have not been captured yet). This value is not compatible with multi-capture and does not allow for coexistence with a third party replication solution. This setting will also result in SQL Server's agent jobs being dropped/disabled, so the transaction log will grow when the capture job is not running and CDC tables and/or Articles are still in place. Do not set this option if another data replication solution is in place or CDC tables are used in the database.

Compare and Refresh from Azure SQL Managed Instance

DSS allows you to perform only Compare and Refresh from Azure SQL Managed Instance (without using Capture). This section describes the configuration requirements for performing only Compare and Refresh from Azure SQL Managed Instance.

Grants for Compare and Refresh from Azure SQL Managed Instance

This section lists the grants required for performing only Compare and Refresh from Oracle database. Based on your requirement, the DSS database User must be granted either of the permission models listed below.

• DbOwner
  The DSS database User must be granted db_owner role for the source database.

• Minimal
  In this permission model, the DSS database User does not need to be a database owner.

If the DSS database User needs to select from tables in another schema (for example if action TableProperties is defined with parameter Schema), then the following <b>select</b> privileges should be granted.

grant select to <em>username</em>; -- Let DSS read all tables

grant select on schema::dbo to <em>username</em>; -- Let DSS only read DBO tables