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

By:   |   Comments   |   Related: > Security


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?


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; 
--Create temporary principal 
CREATE LOGIN login1 WITH PASSWORD = 'P@$$w0rdO1'; 
CREATE USER user1 FOR LOGIN login1; 

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 

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. 
--Display the cookie value. 
SELECT @cookie AS cookie; 
query results

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.

query results

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; 
-- Verify the context switch reverted. 
query results

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Edwin Sarmiento 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

Comments For This Article

get free sql tips
agree to terms