This post demonstrates the use of context switching to provide controlled access to objects to which a user otherwise does not have permissions. To understand how context switching 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 = 'ContextSwtichingDemo')
   DROP DATABASE ContextSwtichingDemo;
GO

CREATE DATABASE ContextSwtichingDemo;
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 ContextSwtichingDemo;
GO

CREATE USER UserX WITHOUT LOGIN;
GO

CREATE USER UserY WITHOUT LOGIN;
GO

Next, a table named TableX is created within SchemaX which is owned by UserX.  The table is populated with data to make the results of a simple SELECT statement against the table easier to see:

CREATE SCHEMA SchemaX AUTHORIZATION UserX;
GO

CREATE TABLE SchemaX.TableX (Column1 int);
GO

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

Next, a stored procedure named ProcY is created within SchemaY which is owned by UserY.  The stored procedure executes a simple SELECT statement against TableX:

CREATE SCHEMA SchemaY AUTHORIZATION UserY;
GO

CREATE PROC SchemaY.ProcY
AS
   SELECT * FROM SchemaX.TableX;
GO

Because ProcY and TableX have different owners, there is no ownership chain between the two.  As a result, when UserY executes stored procedure ProcY, an error message is returned:

EXECUTE AS USER='UserY';
GO

EXEC SchemaY.ProcY;
GO

Msg 229, Level 14, State 5, Procedure ProcY, Line 4
The SELECT permission was denied on the object 'TableX', database 'ContextSwtichingDemo', schema 'SchemaX'.

REVERT;
GO

Altering the stored procedure to employ context switching, access to TableX can be obtained:

ALTER PROC SchemaY.ProcY
WITH EXECUTE AS 'UserX'
AS
   SELECT * FROM SchemaX.TableX;
GO

Now when UserY executes ProcY, the stored procedure executes successfully:

EXECUTE AS USER='UserY';
GO

EXEC SchemaY.ProcY;
GO

REVERT;
GO

The following group of statements cleans up the demonstration environment:

USE master;
GO

DROP DATABASE ContextSwtichingDemo;
GO