Ownership Changes in SQL Server 7.0's Merge Replication

Microsoft’s SQL Server development team explains why ownership changes when you’re using SQL Server 7.0’s merge replication utility and why it doesn’t change in SQL Server 2000.

ITPro Today logo in a gray background | ITPro Today

When I try to set up merge replication in SQL Server 7.0, the replication works fine, but I experience an ownership problem. Here's the scenario. A user named System owns all the database objects in an application named system. I set up replication to connect as sa, and I configure the replication objects—publishers, subscribers, articles, and so on—for system user-owned objects. All the published objects are pushed to the subscriber. These objects publish at the subscriber correctly, but the owner of the subscriber database for those objects changes from system to dbo. Why does the owner change?

You can expect the owner to change in SQL Server 7.0 because the account that you're using to log in to the subscriber is sa. If you set the Merge Agent's subscriber-side connection login with the system account, system should own all the objects. SQL Server 2000, however, includes a way to specify object ownership at the subscriber (in the article's Properties dialog box), even if you use a different account to synchronize the changes.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like