Scenario II
John creates a stored procedure that references a table that he does not own but has SELECT permissions on (the owner of the table is Mary and she has granted select permissions to John).
John grants execute permissions on the stored procedure to Scott. When Scott executes the stored procedure, SQL Server verifies that he (the caller) has permission to execute the stored procedure. Scott has execute permission, but because John is not the owner of the referenced table, SQL Server checks to see whether Scott has permissions on the table. If Scott does not have permissions, the stored procedure statement fails.
1. Login as Mary and grant select permission to John on the students table.
GRANT SELECT ON students TO john
2. Login as John and create a SP ‘stud_sp_john’
CREATE PROCEDURE stud_sp_john
AS
SELECT * FROM Mary.students
3. Grant execute permission on the SP ‘stud_sp_john’ to Scott.
GRANT EXECUTE ON stud_sp_john TO Scott
4. Now login as Scott and execute the SP
EXECUTE John.stud_sp_john
Note: This failure is due to a Broken Ownership Chain
In SQL Server 2005, the SP’s in scenario 1 and 2 can be also written as:
CREATE PROCEDURE stud_sp_john
WITH EXECUTE AS CALLER
AS SELECT * FROM Mary.students
EXECUTE AS CALLER is the default (backward compatible) behavior.
Scenario III
Mary creates a stored procedure that references a table that she does not own (John is owner of a table and has granted select permission to Mary) but has SELECT permissions on. She specifies EXECUTE AS USER = Mary in the CREATE PROCEDURE statement.
Mary grants execute permissions on the stored procedure to another user, Scott. When Scott executes the stored procedure, SQL Server verifies that he has permission to execute the stored procedure; however, permissions for the referenced table are checked for Mary. In this scenario, even though Scott did not have SELECT permissions on the table directly, he was able to access the data through the procedure, because Mary, in whose context the procedure was running, had access to the procedure.
1. Login as Mary and create a SP ‘stud_sp_mary’ with “WITH EXECUTE AS USER = Mary” option.
CREATE PROCEDURE stud_sp_mary
WITH EXECUTE AS USER = Mary
AS
SELECT * FROM students
2. Now grant execute permission on ‘stud_sp_mary’ to Scott
GRANT EXECUTE ON stud_sp_mary TO Scott
3. Login as Scott and execute the SP ‘stud_sp_mary’
EXECUTE Mary.stud_sp_mary