Could not Execute ‘sp_MSadd_replcmds’ – Fun with SQL Replication

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.

Advertisements

One thought on “Could not Execute ‘sp_MSadd_replcmds’ – Fun with SQL Replication

  1. Hi, I found this blog entry in my chasing up the same issue. Adding the sysadmin role fixed to the log reader account fixed the replication issue that I had with MSSQL 2014 temporarily fixed my issue. I removed the sysadmin role and then rebooted and the servers had issues with starting the replication due to the log reader agent. It turns out that I had managed to miss re-adding the db_owner role for the distribution database on all log reader account (as well as the other agent accounts). Once I added the db_owner role for the distribution database back onto all the replication agent accounts – replication began working.
    I appreciate you taking the time to post your findings and work here, it really helped me resolve my issue – and like you said the other information out there on the web was less than helpful. Thank you.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s