Ensure support for stored procedure replication when you create stored procedures with
the execute as {owner | caller} clause of the Adaptive Server
create procedure command.
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.