Post reply

Replicated tables owned by DBO

  • December 5, 2005 at 1:23 pm

    #92675

    I'm implementing merge replicatoin between 2 SQL Server instances. This is working fine, except for 1 issue. The publisher database tables are owned by the TestPartner user and when I run the merge agent the 1st time to create the objects in the subscriber DB, it creates the objects as the DBO user. Replication works fine, between these two, but now my application doesn't function against the subscriber DB, b/c the tables are owned by DBO and not TestPartner. Any idea how to make the merge agent run as the TestPartner user and not as DBO?

  • December 6, 2005 at 2:58 am

    #607889

    Hi Shawn

    Well first off, you need to create the TestPartner account on all subscribers, using same password giving it dbo permissions on the database your replicating to. You then need to tell SQL to use this account when using replication - this is done at the Distributor.

    To do this, use Enterprise Mangler and browse to your Publisher DB and Configure the publication you wish to change (Tools menu\Replication\Configure Publisher, Distributor and Subscribers).Goto the subscriber tab and then find the subscriber you need to change in the list. Click the three dot button and Select "Use SQL Server Authentication". Change the UserName to TestPartner and enter password.

    All object creation etc will now be run using this login.

    Another way to tackle this would be to create a job to run the snapshot agents periodically and change all the ownerships using sp_changeObjectOwner.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply