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 must 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 this DDL command 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 generates a table named schema2.tab1. Therefore, user2, whose name is specified by the ddl_username configuration parameter, must impersonate user1 by issuing this command at the replicate database:
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:
GRANT IMPERSONATE ON USER::user1 TO ddl_user
where 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.