Stored Procedure Replication and the execute as Clause

Adaptive Server Stored Procedure Creation

In Adaptive Server, you can create a stored procedure with the execute as {owner | caller} clause.

If you create a stored procedure:

Without an execute as clause, Adaptive Server resolves the names of objects inside the stored procedure on behalf of the procedure owner. Adaptive Server evaluates permission checks for DML inside the stored procedure on behalf of the procedure caller, and then executes DDL inside the stored procedure on behalf of the procedure caller.
With an execute as owner clause, Adaptive Server resolves the names of objects inside the stored procedure on behalf of the procedure owner. Adaptive Server evaluates permission checks for DML inside the stored procedure on behalf of the procedure owner, and then executes DDL inside the stored procedure on behalf of the procedure owner.
With an execute as caller clause, Adaptive Server resolves the names of objects inside the stored procedure on behalf of the procedure caller. Adaptive Server evaluates permission checks for DML inside the stored procedure on behalf of the procedure caller, and then executes DDL inside the stored procedure on behalf of the procedure caller.

See Executing a Procedure with execute as owner or execute as caller in the Adaptive Server Enterprise Security Administration Guide.

Replication Server Function Replication Definitions

In Replication Server, you can use the create applied function replication definition or create request function replication definition commands to create function replication definitions that describe stored procedures that you want to replicate..

The difference between the applied function replication definition and the request function replication definition is that the function that Replication Server replicates through an applied function replication definition is executed at the replicate site by the maintenance user while the function that Replication Server replicates through a request function replication definition is executed at the replicate site by the same user who executes the primary function at the primary site. See Manage Replicated Functions in the Replication Server Administration Guide Volume 1, and create applied function replication definition and create request function replication definition in the Replication Server Reference Manual

To replicate a stored procedure in a non-warm standby or non-MSA environment, you must create a replication definition and a corresponding subscription. Replication of a stored procedure in a warm standby or MSA environment does not require a replication definition. Only one function replication definition can exist for a specific replicated stored procedure.

Ensuring Successful Stored Procedure Replication

If you use the execute as {owner | caller} clause to create procedures, the identity of the caller at the primary database is not always preserved at the replicate database.

If the identities differ, stored procedure replication may fail because:

The DDL permissions at the replicate database are insufficient for Adaptive Sever to execute the stored procedure DDL.
The replicate database cannot resolve the names of objects inside the stored procedure which results in the database accessing the wrong objects or not finding any objects.
The DML cannot pass the replicate database permission evaluation checks.

For the successful replication of stored procedures:

Carefully evaluate object name resolution or whenever possible, use fully qualified object names when you create stored procedures with the execute as {owner | caller} clause.
Grant the relevant permissions to the:
- Maintenance user if you are using an applied function replication definition.
- Replicate procedure owner if you are using a request function replication definition.