I got MS SQL 2008 running on Windows Server 2008 R2.

Everything is fresh install running on a VM.

I can make a Snapshot Publication without problems. But I am unable to make a Transactional Publication, I get errors even before I try to configure a subscriber. Here's how I did things:

  1. I configure Distribution. (DEFAULT SETTINGS)
  2. I start the New Publication Wizard.

    • Transactional publication
    • I select "Creat a snapshot immediately"
    • I configure the security settings for the Snapshot Agent and Log Reader Agent (Using the Windows account Domain Admin)
    • The Publication is created succesfully, no errors.
    • The "View Snapshot Agent Status" report no errors - 100% success.
    • Going to the Replication Monitor, that's where the errors begin - Error with the Log Reader Agent - "The process could not execute 'sp_replcmds' on 'SERVERNAME'..." [see picture]

What am I doing wrong? Or what am I missing? enter image description here


From BOL:

Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_replcmds.

So, who is the logreader agent running as? That is, how is it connecting to your publisher database? If that user doesn't have db_owner or is a sysadmin, you'll get a failure.

  • Like I said in the original post: I configure the security settings for the Snapshot Agent and Log Reader Agent (Using the Windows account Domain Admin) - If that doesn't answer the question, can you tell me how to check who is the logreader agent running as? – ToastMan Jul 28 '11 at 19:48
  • I took that to mean "as a domain admin, I configured the security". One other thing to check is who the dbo user maps to in your database. Since I see in one of your other replies that this is a restore from somewhere else, it's possible that it doesn't map to a valid login. If that's the case, fix that and see if your problem persists. – Ben Thul Jul 28 '11 at 19:57
  • No idea how.. mind holding my hand? :p But seriously I'm a SQL noob, no idea how to check who the dbo user maps to in the database :( – ToastMan Jul 28 '11 at 20:02
  • Is that what you're looking for? img543.imageshack.us/img543/1971/85318219.jpg – ToastMan Jul 28 '11 at 20:10
  • 1
    OK i'm definitely getting somewhere, I tried again with a different database and it worked! And I compared the dbo properties between the 2 databases, and I noticed that the database with the problem is missing a "Login name" (as seen blank in the screenshot above) so now I just need to figure out how to fill that grayed out field – ToastMan Jul 28 '11 at 20:17

If the subscriber database has been restored from a backup to a different instance, check if the domain admin user is orphaned.

use [subscriber] go exec sp_change_users_login 'report' go

  • The database I'm using is restored from a backup that comes from a SQL 2005, is that the problem? – ToastMan Jul 28 '11 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.