Creating a procedure with references to an object with an unqualified name

In the following example, 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

• Adaptive Server will look for a table named t1 owned by Jane. If jane.t1 does not exist, Adaptive Server will look for dbo.t1.

• If Adaptive Server resolves t1 to dbo.t1, Bill must have permission to insert into t1.

• 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

• Adaptive Server will look for a table named t1 owned by Jane. If jane.t1 does not exist Adaptive Server will look for dbo.t1.

• If Adaptive Server resolves t1 to dbo.t1, permission to insert into t1 must have been granted to Jane.

• If t1 resolves to jane.t1, since the procedure is being executed as owner, Jane has the permission.

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

• Adaptive Server will look for a table named t1 owned by Bill. If bill.t1 does not exist Adaptive Server will look for dbo.t1.

• If Adaptive Server resolves t1 to dbo.t1, Bill must have permission to insert into t1.