Extend the REVERT statement using the WITH COOKIE clause in SQL Server

By: Edwin Sarmiento   |   Updated: 2009-01-06   |   Comments   |   Related: > Security

Problem

In a previous tip on Switching Execution Context using the REVERT clause, you have seen how to switch execution context to a more privileged user and revert back to the original context of the caller within a programmable object. The REVERT clause accepts a parameter WITH COOKIE to provide an even more granular option. How do we use it?

Solution

The example presented in the previous tip presents a simple implementation of elevation of privilege from within a database and reverting it back to the original context of the caller. There are cases where the opposite is the requirement - they use a more privileged user and use EXECUTE AS to run queries as a user with limited privileges. The REVERT statement, in this case, would cause the elevation of privilege. A typical scenario would be to execute a query against linked servers where the account used is granted limited privileges on the remote server. Another scenario I've seen is where an application uses a proxy account to log in to SQL Server and stores the application-related logins as database users. The application switches execution context from the proxy account to the appropriate database user, sometimes called a login-less user, for a more granular control. In fact, this is a Microsoft recommended alternative to using application roles as it is more secured since it is based on permissions and not passwords unlike application roles.

To demonstrate the REVERT statement using the WITH COOKIE parameter, we will use the AdventureWorks database. We will create a temporary login that will be mapped to a database user later on.

USE AdventureWorks; 
GO 
--Create temporary principal 
CREATE LOGIN login1 WITH PASSWORD = 'P@$$w0rdO1'; 
GO 
CREATE USER user1 FOR LOGIN login1; 
GO 

Now that we have the database user, let's log in to the database by switching execution context as user1 using the EXECUTE AS statement. In this particular case, we need to define a cookie and assign it to a variable which we need to store for later retrieval.

DECLARE @cookie varbinary(100); --variable to store the cookie 
--switch execution context, generate cookie and assign it to variable 
EXECUTE AS USER = 'user1' WITH COOKIE INTO @cookie;

You have the option to store the cookie in your application, an external storage such as a file system or a SQL Server database. The important thing is we store the value. This cookie is necessary in order to switch the execution context back to the original caller since we used the WITH COOKIE parameter in the EXECUTE AS statement. Let's verify that the execution context is now switched to user1 and retrieve the cookie value.

-- Verify the context switch. 
SELECT CURRENT_USER AS UserName ; 
--Display the cookie value. 
SELECT @cookie AS cookie; 
GO 

The key thing about this is that in the previous tip, a simple REVERT statement would switch the execution context back to the original caller. Running the REVERT statement in this particular case would case and error.

We need to provide the cookie value which we retrieved from the EXECUTE AS statement in order to switch execution context to the original caller, assuming we stored it somewhere.

-- Use the cookie in the REVERT statement. 
DECLARE @cookie varbinary(100); 
-- Set the cookie value to the one from the SELECT @cookie statement. 
SET @cookie = 0x010000005EE26C9315B528DCAC4529A93780295587F814071D415F468949070829230000; 
REVERT WITH COOKIE = @cookie; 
-- Verify the context switch reverted. 
SELECT CURRENT_USER AS UserName;  
GO 

Notice that you can only switch the execution context back to the original caller if you provide the cookie value initially set using the WITH COOKIE parameter. This concept is similar to the sp_unsetapprole system stored procedure which is associated with application roles.

Next Steps

• Evaluate the use of the EXECUTE AS with the REVERT clause and WITH COOKIE parameter as an alternative to application roles
• Check out REVERT clause in SQL Server Books Online.
• Check out the SQL Server 2005 Security Best Practices whitepaper.
• Check out the first tip in the series - Granting permission with the EXECUTE AS command in SQL Server.

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips