Today I was working on a Microsoft SQL Server 2008 Replication system that’s being implemented at the moment and ran a build against a system. Immediately two of the subscriptions failed with the ominous message below:
The process could not execute 'sp_MSadd_replcmds' on 'DbServerName' Only members of the sysadmin fixed server role can perform this operation.
I tracked down the various permissions for the different agents and everything looked OK and then hooked up SQL Profiler and confirmed that the command was being executed by the correct LogReader account.
After not much help online where most things suggested this was due to a lack of DB owner defined I decided to run this sproc myself in the appropriate context on my test database – this is the exact command captured from SQL Server Profiler.
EXECUTE AS user = 'XXXXX\LogReaderServiceAccount' EXEC sp_MSadd_replcmds 1,0,N'Global',0xLotsOfBinary REVERT
I was immediately pointed to a more helpful error stating that the actual problem was with access to the stored procedure sp_MSsetupnosyncsubwithlsnatdist on the distributor database. This stored procedure has an explicit check for the account to have sysadmin rather than just DBOwner (which is what we have setup).
Granting SysAdmin to the log reader account fixed it but this isn’t what I wanted, so back to resolving this correctly or so I had hoped.
This turned out to be an issue due to one of the subscriptions changing the sync_type to “replication support only” in a recent build and then deploying this against a fresh server where permissions hadn’t been tweaked. According to this MS document when creating a subscription with a sync_type of “replication support only” the log reader agent account needs sysadmin.
Not exactly ideal, but adding the sysadmin role to our build scripts fixed it although it’s not very clear anywhere why this is happening. If you see the error “could not execute ‘sp_MSadd_replcmds'” in the future then worth checking your subscription details.
For those that are more security conscious it looks like this permission is only needed the once (at least in my testing so far) so I just removed the ServerAdmin role as part of our deployment once everything was working and this did the trick.