Mini Post: The Process Could Not Execute sp_replcmds

Good day out there and welcome back to SQL Something!

Today we're going to look at a small error which is related to the permissions on a DB that is being used for publishing in SQL replication.

After setting up my publication and a subscriber to be used for transactional replication, I noticed that transactions were not being replicated. Upon checking the "View Log Agent Status" (right click the publication and choose the option), I saw the following error: "The process could not execute sp_replcmds".

Fig. 1: The Error in Log Reader Agent. It can also be found in the Replication Monitor, under the 'Agents' tab.

I checked the users that ran the different jobs/services dealing with replication and they seemed to be correct. Next I checked the owners of the DBs involved, both the one being published and the one subscribing.

On the properties for the DB that was being published, on the 'General' tab I saw that the owner was set to the correct username.

Checking the 'Files' tab however revealed that the owner was not set (it was blank):

Fig. 2: No owner has been set...

This could be in part because the DB in question was restored from a backup taken from another server.

The solution therefore was to use one of the following:

• Set the Owner via management studio (using this same 'Files' tab in Properties), OR
• Run the following in a Query window: ALTER AUTHORIZATION ON DATABASE::DatabaseName TO [YourDomain\YourUser]

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)