Example of Procedure with execute as

Create a procedure that invokes a nested procedure in another database with a fully qualified name.

In the following example, Jane creates a procedure that invokes a nested procedure in another database with a fully qualified name. The login associated with Jane resolves to user Jane in otherdb.

This example uses execute as owner:

create procedure p master

    with execute as owner

    as exec otherdb.jim.p_child

grant execute on p master to bill

Bill executes the procedure:

SAP ASE checks that user Jane in otherdb has execute permission on jim.p_child.
If jim.p_child has been created execute as owner then p_child will be executed on behalf of Jim.
If jim.p_child has been created execute as caller then p_child will execute on behalf of Jane.

In the following example, Jane creates the same procedure as above using execute as caller. The login associated with user Bill in the current database resolves to user Bill in otherdb:

create procedure p master

    with execute as caller

    as exec otherdb.jim.p_child

grant execute p master to bill

Bill executes the procedure:

exec jane.insert p

SAP ASE checks that Bill in otherdb has execute permission on jim.p_child.
If jim.p_child has been created execute as owner then p_child will be executed on behalf of Jim.
If jim.p_child has been created execute as caller then p_child will execute on behalf of Bill.