Setting ddl_username and ddl_password

To replicate DDL in Microsoft SQL Server, in addition to setting the value of pdb_setrepddl to enable, set the Replication Agent ddl_username and ddl_password parameters. The ddl_username parameter is the replicate database user name included in LTL for replicating DDL commands to the replicate or target database.

In addition to the permission to execute all replicated DDL commands at the replicate database, the ddl_username should also have the impersonate permission granted for all users whose DDL commands may be replicated to the replicate database. This impersonate permission is necessary to switch session context in the replicate database when executing a DDL command. This user switches context to apply the DDL command using the same privileges and default schema settings as the user who executed the DDL command at the primary database. To provide this context switch, the ddl_username user must have permission to execute the execute as user Microsoft SQL Server command for any user who might execute DDL commands to be replicated from the primary database.

For example, user1 with a default schema of schema1 executes the following DDL at the primary database:

create table tab1 (id int)

This results in the creation of a table named schema1.tab1 at the primary database. At the replicate database, user2 with a default schema of schema2, cannot immediately execute this DDL because it will generate a table named schema2.tab1. Therefore, user2, whose name is specified by the ddl_username configuration parameter, must first execute the following command at the replicate database to impersonate user1:

execute as user = 'user1'

The DDL can then be executed with the correct schema by user2 at the replicate database, generating a table named schema1.tab1.

See the Replication Agent Reference Manual.

There are two ways to grant impersonate permission to the ddl_username user:

• You can grant database owner permission to the to the ddl_username user. In doing this, you implicitly grant impersonate permission.

• Alternately, you can grant impersonate permission explicitly with the following Microsoft SQL Server command:

  GRANT IMPERSONATE ON USER::user1 TO ddl_user
  

  Here, user1 is a user whose DDL is expected to be replicated to the replicate database, and ddl_user is the ddl_username user.

When you replicate DDL in Microsoft SQL Server, use Microsoft SQL Server as the replicate database. You cannot replicate DDL commands from Microsoft SQL Server to non-Microsoft SQL Server replicate databases.

The following database-scope DDL commands are not replicated:

• ALTER_APPLICATION_ROLE

• ALTER_ASSEMBLY

• ALTER_AUTHORIZATION_DATABASE

• ALTER_CERTIFICATE

• CREATE_APPLICATION_ROLE

• CREATE_ASSEMBLY

• CREATE_CERTIFICATE

• CREATE_EVENT_NOTIFICATION

• DROP_EVENT_NOTIFICATION

The following server-scope DDL commands are not replicated:

• ALTER_AUTHORIZATION_SERVER

• ALTER_DATABASE

• ALTER_LOGIN

• CREATE_DATABASE

• CREATE_ENDPOINT

• CREATE_LOGIN

• DENY_SERVER

• DROP_DATABASE

• DROP_ENDPOINT

• DROP_LOGIN

• GRANT_SERVER

• REVOKE_SERVER

Any object owned by users defined in the list of non-replicated users is not replicated. You can modify this list using the pdb_ownerfilter command. In addition, Sybase has provided a default list of owners whose objects will not be replicated. Use the pdb_ownerfilter command to return, add, or remove the list of owners whose objects will not be replicated. See the Replication Agent Reference Manual.