Setting ddl_username and ddl_password

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

In addition to the permission to execute all replicated DDL commands at the standby database, the ddl_username should also have the impersonate permission granted for all users whose DDL commands may be replicated to the standby database. This impersonate permission is necessary to switch session context in the standby 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 standby 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 standby database to impersonate user1:

execute as user = 'user1'

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

See the Mirror Replication Agent Reference Manual for details on setting these parameters.

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 standby database, and ddl_user is the ddl_username user.

When you replicate DDL in Microsoft SQL Server, you must use Microsoft SQL Server as the standby database. You cannot replicate DDL commands from Microsoft SQL Server to non-Microsoft SQL Server standby 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. You can use the pdb_ownerfilter command to return, add, or remove the list of owners whose objects will not be replicated. See the Mirror Replication Agent Reference Manual for more information.