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.
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:
- Administer bulk operations
- Alter any connection
- Alter settings
- Connect SQL
- Control Server
Update and insert rows into existing tables on Source replicating to Target.
Replication status ok!