Ownership Chains in SQL Server 2005

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