Using revert to switch stored procedure execution contexts in SQL Server

Problem

Previously, we learned how to grant permissions using the EXECUTE AS command in SQL Server 2005, and you've seen how to grant granularity permissions with the EXECUTE AS clause. It is important to switch contexts within a programmable object such as a stored procedure or function, especially if the user accessing the stored procedure does not have sufficient permissions to run a block of code. But in order to make it more granular, we allow switching to the execution context only when it is needed and when it is returned to the caller's original execution context. How are we going to make this happen?

Expert answers

The EXECUTE AS clause in SQL Server 2005 gives us the option to control the security context in which the code module executes. To extend this concept, we use the revert clause to switch the execution context back to the caller who executed the last EXECUTE AS statement. This allows us to allow the user to play a highly privileged account when needed and to return to the original execution context with limited permissions. For example, a stored procedure that makes some changes in a database based on some business logic. Before you run the code block to change the data, you need to create a database backup that can roll back these changes at any time you need. Because we do not want to give more permissions to the user who is going to execute the stored procedure, we will use the EXECUTE AS clause to play an account with more privileges, have the account do a database backup, and use the revert clause to switch back to the caller's original execution context. Let's take a look at a sample script to demonstrate how these two clauses work.

First, we log on to SQL Server. One of them is a regular user with very small permissions, and the other is a member of the system Administrator role.

USE master
GO
--Add Windows logins to SQL Server
IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'SQLSRV90\SQLUser1')
CREATE LOGIN [SQLSRV90\SQLUser1]
FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks
IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'SQLSRV90\SQLDBA')
CREATE LOGIN [SQLSRV90\SQLDBA]
FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks

Next, we increase the user's login in the AdventureWorks database.

USE AdventureWorks
--Add the new logins to the AdventureWorks database
CREATE USER SQLUser1 FOR LOGIN [SQLSRV90\SQLUser1]
CREATE USER SQLDBA FOR LOGIN [SQLSRV90\SQLDBA]
--Add SQLDBA Windows account to the db_owner role
EXEC sp_addrolemember 'db_owner', 'SQLDBA'
GO