Ownership Chain Demo

This post demonstrates the use of ownership chains to provide controlled access to objects to which a user otherwise does not have permissions. To understand how ownership chains can be employed to improve the security of database applications, please review this post.

The first step in the demonstration is to create an empty database.  In this database, a few objects and user accounts will later be created:

USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'OwnershipChainDemo')
   DROP DATABASE OwnershipChainDemo;
GO

CREATE DATABASE OwnershipChainDemo;
GO

The next step is to create a few user accounts within the database.  To keep things simple, these will be created without an associated login:

USE OwnershipChainDemo;
GO

CREATE USER UserX WITHOUT LOGIN;
GO

CREATE USER UserY WITHOUT LOGIN;
GO

Next, two objects, a table named TableX and a stored procedure named ProcX, will be created.  ProcX will issue a simple SELECT statement against TableX and TableX will be populated with a small amount of data so the results of that query are easier to see.

TableX and ProcX will both be created within SchemaX.  SchemaX is owned by UserX so that TableX and ProcX are owned by the same owner, UserX, creating an ownership chain between these two objects:

CREATE SCHEMA SchemaX AUTHORIZATION UserX;
GO

CREATE TABLE SchemaX.TableX (Column1 int);
GO

INSERT INTO SchemaX.TableX VALUES (1),(2),(3);
GO

CREATE PROC SchemaX.ProcX
AS
SELECT * FROM SchemaX.TableX;
GO

The last step is to grant EXECUTE permissions on stored procedure ProcX to UserY:

GRANT EXECUTE ON OBJECT::SchemaX.ProcX TO UserY;
GO

By impersonating UserY, the ownership chain can now be demonstrated:  

EXECUTE AS USER='UserY';
GO

UserY does not have direct access to TableX as demonstrated by the error message associated with this statement:

SELECT * FROM SchemaX.TableX;
GO

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'TableX', database 'OwnershipChainDemo', schema 'SchemaX'.

However, UserY does have EXECUTE permissions on ProcX which issues this same SELECT statement:

EXEC SchemaX.ProcX;
GO

Results are returned by ProcX simply because of the ownership chain.

The following group of statements ends impersonation and cleans up the demonstration environment:

REVERT;
GO

USE master;
GO

DROP DATABASE OwnershipChainDemo;
GO