Changing the schema of an article from DBO to UserDefinedSchema will be reverted back to DBO

Consider a scenario where one wants to change the Destination Object Owner schema of all the articles in a publication using “Set Properties of All Table Articles” from DBO to any other user defined schema. What you will find is that under Publication properties if you select “Set Properties of All Table Articles”, you will see the changes reverted back to DBO. The articles would be created in the desired schema at the destination and the replication would work normally.

Scenario

Type of replication: Transactional Replication.

Publisher: Oracle 10G; Windows Server 2003 SP2

Subscriber: SQL Server 2005 SP2; Windows Server 2003 Sp2

Repro: In the publication properties->Article Properties->Set Properties of All Table Articles->Change the Destination Object Owner from DBO to UserDefinedSchema. Close the Publication properties window. Open the Properties window again and you will see DBO.

Why SQL has such behaviour?

EXPLANATION

Now the question arises, why SQL have given the option to change the owner of the schema in the “Set Properties of All Table Articles” option when the changes are not permanent? The same thing we do in “Set Properties of Highlighted Table Article” make sure the changes are permanent.

The answer to this is:

1. Every new article will have DBO as schema by default.

2. SQL Server expects that we may add some more articles whose schema would be DBO. This means once you change the schema to user defined, it will make sure that all the articles will have that schema but later it will revert back to the DBO. This facility will only ensure that you don’t have to change the schema of all the articles one by one every time. So, this is just a facility by SQL Server.

3. This window is the global window for all articles and thus can’t list different settings for different articles in the same window. Rather what we should be concentrating at is the window for specific tables “Set Properties of Highlighted Table Article”.

From BooksOnLine:

Destination object owner

The schema under which the object is created in the subscription database. The default is the schema to which the object belongs in the publication database, with the following exceptions:

For articles in merge publications with compatibility level lower than 90: by default, the owner is left blank and is specified as dbo during the creation of the object on the Subscriber.

For articles in Oracle publications: by default, the owner is specified as dbo.

For articles in publications that use character mode snapshots (which are used for non-SQL Server Subscribers and SQL Server Mobile Subscribers): by default, the owner is left blank. The owner defaults to the owner associated with the account used by the Distribution Agent or Merge Agent to connect to the Subscriber. This option cannot be changed for articles in publications that are enabled for peer-to-peer transactional replication.

Sumit Sarabhai
SE, Microsoft Sql Server

Reviewed by
Rakesh Singh
Technical Lead, Microsoft Sql Server