Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


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

By:   |   Last Updated: 2009-01-06   |   Comments   |   Related Tips: More > 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 = '[email protected]$$w0rdO1';

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;

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

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

Last Updated: 2009-01-06

get scripts

next tip button

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.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools