We're here to help!

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

Follow
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

 

 

2.The properties of the dbvrep user created

 

 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!

 

 

 

Have more questions? Submit a request

Comments