Tutorial: Ownership Chains and Context Switching
This tutorial uses a scenario to illustrate SQL Server security concepts involving ownership chains and user context switching. For more information about ownership chains, see Ownership Chains. For more information about context switching, see Context Switching (Database Engine).
Note
To run the code in this tutorial you must have both Mixed Mode security configured and the AdventureWorks2008R2 database installed. For more information about Mixed Mode security, see Choosing an Authentication Mode.
Scenario
In this scenario, two users need accounts to access purchase order data stored in the AdventureWorks2008R2 database. The requirements are as follows:
The first account (TestManagerUser) must be able to see all details in every purchase order.
The second account (TestEmployeeUser) must be able to see the purchase order number, order date, shipping date, product ID numbers, and the ordered and received items per purchase order, by purchase order number, for items where partial shipments have been received.
All other accounts must retain their current permissions.
To fulfill the requirements of this scenario, the example is broken into four parts that demonstrate the concepts of ownership chains and context switching:
Configuring the environment.
Creating a stored procedure to access data by purchase order.
Accessing the data through the stored procedure.
Resetting the environment.
Each code block in this example is explained in line. To copy the complete example, see Complete Example at the end of this tutorial.
1. Configure the Environment
Use SQL Server Management Studio and the following code to open the AdventureWorks2008R2 database, and use the CURRENT_USER Transact-SQL statement to check that the dbo user is displayed as the context.
USE AdventureWorks2008R2;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
For more information about the CURRENT_USER statement, see CURRENT_USER (Transact-SQL).
Use this code as the dbo user to create two users on the server and in the AdventureWorks2008R2 database.
CREATE LOGIN TestManagerUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khx';
GO
CREATE USER TestManagerUser
FOR LOGIN TestManagerUser
WITH DEFAULT_SCHEMA = Purchasing;
GO
CREATE LOGIN TestEmployeeUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
GO
CREATE USER TestEmployeeUser
FOR LOGIN TestEmployeeUser;
GO
For more information about the CREATE USER statement, see CREATE USER (Transact-SQL). For more information about the CREATE LOGIN statement, see CREATE LOGIN (Transact-SQL).
Use the following code to change the ownership of the Purchasing schema to the TestManagerUser account. This allows that account to use all Data Manipulation Language (DML) statement access (such as SELECT and INSERT permissions) on the objects it contains. Since this does not include Data Definition Language (DDL) permissions, TestManagerUser is explicitly granted rights on the PurchaseOrderHeader and PurchaseOrderDetail tables, as well as the ability to create stored procedures.
/* Change owner of the Purchasing Schema to TestManagerUser */
ALTER AUTHORIZATION
ON SCHEMA::Purchasing
TO TestManagerUser;
GO
/* Grant permissions to TestManagerUser on these objects with GRANT option */
GRANT ALL
ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderHeader
TO TestManagerUser
WITH GRANT OPTION;
GO
GRANT ALL
ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderDetail
TO TestManagerUser WITH GRANT OPTION;
GO
/* Note: DML works fine with Schema owner, but not DDL. */
GRANT CREATE PROCEDURE
TO TestManagerUser
WITH GRANT OPTION;
GO
For more information about the GRANT statement, see GRANT (Transact-SQL). For more information on database schemas, see Schemas (Database Engine). For more information about stored procedures, see Stored Procedures (Database Engine).
2. Create a Stored Procedure to Access Data
You have two ways of allowing a user to switch contexts within a database: SETUSER or EXECUTE AS. Using the SETUSER statement requires the caller to have membership in the sysadmin fixed server role or be the dbo account. EXECUTE AS requires IMPERSONATE permissions. For more information about these concepts, see EXECUTE AS vs. SETUSER.
Use the EXECUTE AS statement in the following code to change the context to TestManagerUser and create a stored procedure showing only the data required by TestEmployeeUser. To satisfy the requirements, the stored procedure accepts one variable for the purchase order number and does not display financial information, and the WHERE clause limits the results to partial shipments.
EXECUTE AS LOGIN = 'TestManagerUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int
AS
BEGIN
SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate
, b.ProductID, b.OrderQty, b.ReceivedQty
FROM Purchasing.PurchaseOrderHeader a
INNER JOIN Purchasing.PurchaseOrderDetail b
ON a.PurchaseOrderID = b.PurchaseOrderID
WHERE b.OrderQty > b.ReceivedQty
AND @ProductID = b.ProductID
ORDER BY b.ProductID ASC
END
GO
Currently TestEmployeeUser does not have access to any database objects. The following code (still in the TestManagerUser context) grants the user account the ability to query base-table information through the stored procedure.
GRANT EXECUTE
ON OBJECT::Purchasing.usp_ShowWaitingItems
TO TestEmployeeUser;
GO
The stored procedure is part of the Purchasing schema, even though no schema was explicitly specified, because TestManagerUser is assigned by default to the Purchasing schema. You can use system catalog information to locate objects, as shown in the following code.
SELECT a.name AS 'Schema'
, b.name AS 'Object Name'
, b.type AS 'Object Type'
FROM sys.schemas a
INNER JOIN sys.objects b
ON a.schema_id = b.schema_id
WHERE b.name = 'usp_ShowWaitingItems';
GO
For more information about system catalogs, see Querying the SQL Server System Catalog.
With this section of the example completed, the code switches context back to dbo using the REVERT statement.
REVERT;
GO
For more information about the REVERT statement, see REVERT (Transact-SQL).
3. Access Data Through the Stored Procedure
TestEmployeeUser has no permissions on the AdventureWorks2008R2 database objects other than a login and the rights assigned to the public database role. The following code returns an error when TestEmployeeUser attempts to access base tables.
EXECUTE AS LOGIN = 'TestEmployeeUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* This won't work */
SELECT *
FROM Purchasing.PurchaseOrderHeader;
GO
SELECT *
FROM Purchasing.PurchaseOrderDetail;
GO
Because the objects referenced by the stored procedure created in the last section are owned by TestManagerUser by virtue of the Purchasing schema ownership, TestEmployeeUser can access the base tables through the stored procedure. The following code, still using the TestEmployeeUser context, passes purchase order 952 as a parameter.
EXEC Purchasing.usp_ShowWaitingItems 952
GO
4. Reset the Environment
The following code uses the REVERT command to return the context of the current account to dbo, and then resets the environment.
REVERT;
GO
ALTER AUTHORIZATION
ON SCHEMA::Purchasing TO dbo;
GO
DROP PROCEDURE Purchasing.usp_ShowWaitingItems
GO
DROP USER TestEmployeeUser;
GO
DROP USER TestManagerUser;
GO
DROP LOGIN TestEmployeeUser;
GO
DROP LOGIN TestManagerUser;
GO
Complete Example
This section displays the complete example code.
Note
This code does not include the two expected errors that demonstrate the inability of TestEmployeeUser to select from base tables.
/*
Script: UserContextTutorial.sql
Author: Microsoft
Last Updated: Books Online
Conditions: Execute as DBO or sysadmin in the AdventureWorks database
Section 1: Configure the Environment
*/
USE AdventureWorks2008R2;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* Create server and database users */
CREATE LOGIN TestManagerUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khx';
GO
CREATE USER TestManagerUser
FOR LOGIN TestManagerUser
WITH DEFAULT_SCHEMA = Purchasing;
GO
CREATE LOGIN TestEmployeeUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
GO
CREATE USER TestEmployeeUser
FOR LOGIN TestEmployeeUser;
GO
/* Change owner of the Purchasing Schema to TestManagerUser */
ALTER AUTHORIZATION
ON SCHEMA::Purchasing
TO TestManagerUser;
GO
/* Grant permissions to TestManagerUser on these objects with GRANT option */
GRANT ALL
ON OBJECT::AdventureWorks2008R2.Purchasing.PurchaseOrderHeader
TO TestManagerUser
WITH GRANT OPTION;
GO
GRANT ALL
ON OBJECT::AdventureWorks2008R2.Purchasing.PurchaseOrderDetail
TO TestManagerUser WITH GRANT OPTION;
GO
/* Note: DML works fine with Schema owner, but not DDL */
GRANT CREATE PROCEDURE
TO TestManagerUser
WITH GRANT OPTION;
GO
/*
Section 2: Switch Context and Create Objects
*/
EXECUTE AS LOGIN = 'TestManagerUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int
AS
BEGIN
SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate
, b.ProductID, b.OrderQty, b.ReceivedQty
FROM Purchasing.PurchaseOrderHeader a
INNER JOIN Purchasing.PurchaseOrderDetail b
ON a.PurchaseOrderID = b.PurchaseOrderID
WHERE b.OrderQty > b.ReceivedQty
AND @ProductID = b.ProductID
ORDER BY b.ProductID ASC
END
GO
/* Give the employee the ability to run the procedure */
GRANT EXECUTE
ON OBJECT::Purchasing.usp_ShowWaitingItems
TO TestEmployeeUser;
GO
/* Notice that the stored procedure is located in the Purchasing
schema. This also demonstrates system catalogs */
SELECT a.name AS 'Schema'
, b.name AS 'Object Name'
, b.type AS 'Object Type'
FROM sys.schemas a
INNER JOIN sys.objects b
ON a.schema_id = b.schema_id
WHERE b.name = 'usp_ShowWaitingItems';
GO
/* Go back to being the dbo user */
REVERT;
GO
/*
Section 3: Switch Context and Observe Security
*/
EXECUTE AS LOGIN = 'TestEmployeeUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
EXEC Purchasing.usp_ShowWaitingItems 952
GO
/*
Section 4: Clean Up Example
*/
REVERT;
GO
ALTER AUTHORIZATION
ON SCHEMA::Purchasing TO dbo;
GO
DROP PROCEDURE Purchasing.usp_ShowWaitingItems
GO
DROP USER TestEmployeeUser;
GO
DROP USER TestManagerUser;
GO
DROP LOGIN TestEmployeeUser;
GO
DROP LOGIN TestManagerUser;
GO
See Also