SQL Server with Mr. Denny

Jun 18 2012   2:00PM GMT

The process could not execute ‘sp_replcmds’ on …

Denny Cherry Denny Cherry Profile: Denny Cherry


When setting up SQL Server replication you might see an error message from the Transactional Replication Log Reader Agent which reads like the following.

Error messages:

  • The process could not execute ‘sp_replcmds’ on ”. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
    Get help: http://help/MSSQL_REPL20011
  • Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517)
    Get help: http://help/15517
  • The process could not execute ‘sp_replcmds’ on ”. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
    Get help: http://help/MSSQL_REPL22037

Often this error message can come from the database not having a valid owner, or the SQL Server is not being able to correctly identify the owner of the database. Often this is easiest to fix by changing the database owner by using the sp_changedbowner system stored procedure as shown below. The sa account is a reliable account to use to change the ownership of the database to.

USE PublishedDatabase
EXEC sp_changedbowner 'sa'

Once the database ownership has been changed the log reader will probably start working right away. If it doesn’t quickly restarting the log reader should resolve the problem.

While this does require changes to the production database, there is no outage required to make these changes.


6  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • mbourgon
    Dangit! I've been bit by this - never thought of that. Many thanks, MrDenny.
    20 pointsBadges:
  • vishnugupthan
    HI Denny, 

    Thanks for the note and it helped me to resolve this issue in one of my prod environment. But I have once query , the old DB owner's id was deactivated long back but replication was running fine till yesterday. Then any idea, how can the replication fail all of a sudden with this error since yesterday. 

    -Vishhnu Gupthan 
    10 pointsBadges:
  • Denny Cherry
    Replication can be really finiky about things. It can work find for a while but then have issues. Usually because something changed, like the database owner isn't valid anymore but replication will keep working until the distribution agent is restarted, then it'll fail.  And this could be days or months later.
    69,055 pointsBadges:
  • LouisKim
    Thanks!! It's Working
    10 pointsBadges:
  • MrPutinGorb
    there is an impact, because it will lock your db.
    10 pointsBadges:
  • Denny Cherry
    I never said there would be no impact, and of course, there are locks taken. But there's no outage required to do this (or at least I haven't seen one of the systems that I've had to do this on).
    69,055 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: