Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          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:   |   Read Comments   |   Related Tips: More > Security

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


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



Last Update:


signup button

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools