Replication Server Support for Transferring Object Ownership

Replication Server replicates the DDL changes generated by alter... modify owner so that the transfers in object ownership at the primary Adaptive Server database are applied to the replicate Adaptive Server database. However, if there is a change in object ownership for an object qualified with an owner, in a replication definition where the owner name is required, Replication Server does not automatically alter the replication definition to reflect the change.

Suppose you create a replication definition for the authors table with Mario as the owner—mario.authors, and subsequently the database adminstrator transfers the table owner to Angela. As a result, Replication Server cannot replicate any insert, delete, or update operations to the replicate authors table because Replication Server receives these operations for replication to angela.authors but the replication definition still has mario.authors.

Coordination of Object Ownership Transfer and Replication Definition Changes

To ensure that data replicates continuously and correctly, coordinate any changes to object ownership with corresponding changes to affected replicated definitions and database replication definitions.

Executing rs_send_repserver_cmd at the primary database allows you to order the change to the replication definition to be always after executing alter... modify owner. It is more difficult to coordinate the transfer of object ownership with the modifications to the replication definition or database replication definition if you execute alter replication definition or alter database replication definition at the Replication Server.

At the primary database, immediately after using alter... modify owner, execute the rs_send_repserver_cmd stored procedure that in turn executes the alter replication definition or alter database replication definition command containing the alter owner from curent_owner to new_owner clause to change the object owner in the replication definition or database replication definition:

alter replication definition –

exec rs_send_repserver_cmd ‘alter replication 
definition replication_definition_name
alter [primary] owner from current_owner to new _owner'

alter database replication definition –
```
exec rs_send_repserver_cmd ‘alter database replication 
definition database_replication_definition_name
alter owner from current_owner to new _owner [for tablename]'
```
Omit the for tablename option if you want to change the owners for all tables in the database replication definition .

Altering the owner in the replication definition creates a new version of the replication definition. If there are already multiple versions of the replication definition, Replication Server only modifies the current version to ensure that any data in the inbound queue replicates with the proper replication definition version. This allows for the transfer of table ownership to occur without having to drain the queues.

Wildcards for Changing Multiple Replication Definitions

If you use alter... modify owner to transfer the ownership of multiple tables, you can use the asterisk "*" wildcard character to replace the replication definition name in alter replication definition to modify the owner in multiple replication definitions affected by the ownership transfer, where old_owner is the same for all the replication definitions:

exec rs_send_repserver_cmd ‘alter replication 
definition *
alter owner with primary at data_server.database
from old_owner to new _owner’

Check for Affected Replication Definitions

Before you use the wildcard to change multiple repliction definitions, execute the rs_helpreptable stored procedure in the RSSD with a wildcard "*" to list all the replication definitions at the primary database that have the same current_owner value:

 rs_helpreptable primary_database, current_owner, '*'