SQL Server 2008 replication failing with: process could not execute 'sp_replcmds'

I have an issue with SQL replication that I am having trouble fixing. What I am doing is restoring two DBs from a production backup, and then installing replication between them. The replication seems to be configured without any errors, but when I look at the sta开发者_如何学Gotus I see error messages like this:

Error messages:

The process could not execute 'sp_replcmds' on 'MYSERVER1'. 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 'MYSERVER1'. Get help: http://help/MSSQL_REPL22037

What does this mean?

When I had this problem, my database didn't have an owner set properly. I had restored a database from another windows domain, right clicked the database -> properties and verified in the "general" tab that the owner was set correctly. However, in the "files" tab, owner was not set at all. As soon as I set it, replication was running without problems.

'dbo' maps to a login that is invalid. If you run select suser_sname(owner_sid) from sys.databases, you probaly get NULL for those two DBs. You need to change 'dbo' to a valid login. Run, on both databases:

ALTER AUTHORIZATION ON DATABASE::[<dbname>] TO [sa]

The easiest way to correct the error is to use ALTER AUTHORIZATION on the databases which have the NULL login match for dbo.

I found that if you use article filters, you must use a unique filter name. Once I changed the filter name to be unique across all articles, it fixed this issue.

In our case the service account that the SQL instance was running on got locked. Once unlocked and we stopped/restarted the LogReader SQL agent jobs then things started flowing again.