31

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 status 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?

Wayne Conrad
  • 103,207
  • 26
  • 155
  • 191
skb
  • 30,624
  • 33
  • 94
  • 146

5 Answers5

67

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.

Mattias Nordqvist
  • 898
  • 6
  • 14
27

'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]
Brann
  • 31,689
  • 32
  • 113
  • 162
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 1
    Doing that will change the owner of the DB to sa, which I don't want. The owner of the DB is currently my personal account, and I want it to stay that way. Could I just re-associate dbo somehow? – skb Apr 28 '10 at 20:39
  • 1
    You can associate it to any account you want, but it has to be a valid NT account. Does suser_sname(owner_sid) return NULL? Then is an invalid account and you *must* change it. – Remus Rusanu Apr 28 '10 at 20:49
2

enter image description here

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

Vishe
  • 3,383
  • 1
  • 24
  • 23
1

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.

Qantas 94 Heavy
  • 15,750
  • 31
  • 68
  • 83
0

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.

E Elhassan
  • 1