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

By:   |   Updated: 2009-01-06   |   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

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

Comments For This Article


Recommended Reading

Enabling xp_cmdshell in SQL Server

Understanding SQL Server fixed database roles

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

SQL Server Permissions List for Read and Write Access for all Databases

List SQL Server Login and User Permissions with fn_my_permissions

get free sql tips
agree to terms