Example with execute as Omitted

Create a procedure with references to an object with an unqualified name.

This is an example where the procedure has no execute as clause.

create procedure insert p

    insert t1 (c1) values (100)

grant execute on insert p to bill

Bill executes the procedure:

exec jane.insert p

SAP ASE will look for a table named t1 owned by Jane. If jane.t1 does not exist, SAP ASE will look for dbo.t1.
If SAP ASE resolves t1 to dbo.t1, permission to insert into t1 must be held by Bill.
If t1 resolves to jane.t1, Bill will have implicit insert permission because of the ownership chain between jane.insert_p and jane.t1.

In the following example, Jane creates the same procedure as above with execute as owner:

create procedure insert p

    with execute as owner as

    insert t1 (c1) values (100)

grant execute on insert p to bill

Bill executes the procedure:

exec jane.insert p

SAP ASE will look for a table named t1 owned by Jane. If jane.t1 does not exist SAP ASE will look for dbo.t1.
If SAP ASE resolves t1 to dbo.t1, permission to insert into t1 must been granted to Jane.

In the following example, Jane creates the same procedure as above with execute as caller:

create procedure insert p

    with execute as caller as

    insert t1 (c1) values (100)

grant execute on insert p to bill

Bill executes the procedure:

exec jane.insert p

SAP ASE will look for a table named t1 owned by Bill. If bill.t1 does not exist SAP ASE will look for dbo.t1.
If SAP ASE resolves t1 to dbo.t1, Bill must have permission to insert into t1.