Ownership Chains in SQL Server 2005

By : Amol Kulkarni May 25, 2005

Printer friendly

When multiple database objects access each other sequentially, the sequence is known as a "chain." Although such chains have no independent existence, when SQL Server is traversing the links in a chain, it evaluates user permissions on the constituent objects differently than it would if it were accessing them separately. These differences have important implications for managing security.

In the article, we will take a look at how ownership chains in SQL Server 2005 work. For demonstration purposes, we will use 3 users (Mary, John and Scott), and take a look at three different scenarios.

Four Different Scenarios

If you like, you can follow along with this article by running the following code in a test SQL Server database. This creates the three users we need for our scenario's below.

sp_addlogin 'Mary','mary123','AdventureWorks'

GO

USE AdventureWorks

GO

sp_grantdbaccess 'Mary'

GO

sp_addrolemember 'db_ddladmin','Mary'

Scenario I

An unbroken ownership chain is one in which the owner of the calling object is also the owner of all the referenced objects. For example, Mary creates a stored procedure that references a table she owns.

She grants execute permissions on the stored procedure to another user, John. When John executes the stored procedure, SQL Server verifies that he (the caller) has permission to execute the stored procedure. Because John has permissions on the stored procedure and because the stored procedure and referenced table have the same owner, no additional permission checking is performed and the statement succeeds. In other words, when Mary granted permissions on the stored procedure to John, she indirectly granted permissions on the referenced table (which she also owns).

1.  Login as Mary and create a table and an SP.            

CREATE TABLE students                  --Creating table students

(rollno INT

,name VARCHAR(30)

,address VARCHAR(100))

GO

INSERT INTO students                     --Inserting records in students table

SELECT '101','ABC','LA' UNION

SELECT '102','XYZ','LA' UNION

SELECT '103','PQR','LA'

GO

CREATE PROCEDURE stud_sp          --Creating SP stud_sp

AS

SELECT * FROM students

             GRANT EXECUTE ON stud_sp TO john
 

3.  Now login as John and execute the SP ‘stud_sp’.
            

             EXECUTE Mary.stud_sp