Replication on SQL server with DB user having explicit privileges instead of sysadmin role.

Problem Description

This KB article discusses an example where one needs to change db user used for replication, by revoking the sysadmin role and grant explicit permissions instead, to ensure all works fine.

Solution

1. Create dbvrep user connected as “SA” under 

Security>Logins

Open

2. The 

properties of the dbvrep user created

Open

3.

Server Roles – uncheck sysadmin

4.

User mapping – checked my data DB (in this example charm) and the dbvrep schema DB 

5. Securables 

– ensure you have the server in this box.

Under explicit Permissions for <ServerName> check:

1. Administer bulk operations

2. Alter any connection

3. Alter settings

4. Connect SQL

5. Control Server

6. Status:

Update and insert rows into existing tables on Source replicating to Target.

Replication status ok!