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

 

Switching Stored Procedure Execution Context in SQL Server using the REVERT clause


By:   |   Read Comments (2)   |   Related Tips: More > Stored Procedures

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


Problem
In the Granting permission with the EXECUTE AS command in SQL Server 2005 tip, you have seen how you can grant granular permissions using the EXECUTE AS clause. Context switching within a programmable object such as a stored procedure or function is important especially if the user calling the stored procedure does not have enough privileges to run a code block. But to make it more granular, we allow switching execution context only when needed and revert back to the original execution context of the caller. How do we do it?

Solution
The EXECUTE AS clause in SQL Server 2005 has given us the options to control the security context for code module execution. Extending that concept, we use the REVERT clause to switch the execution context back to the caller of the last EXECUTE AS statement. This enables us to allow users to impersonate highly privileged accounts only when needed and revert back to the original execution context with limited privileges. Take for example a stored procedure that does some changes in the database based on some business logic. Before running the code block to change the data, you need to generate a database backup that can be used to rollback the change whenever necessary. As we do not want to give more privilege to the user that would run the stored procedure, we will use the EXECUTE AS clause to impersonate a more privileged account to do the database backup and REVERT clause to switch back to the original execution context of the caller. Let's look at a sample script to demonstrate how that works.

We'll first add logins to SQL Server. One will be an ordinary user with very minimal privileges and another would be a member of the sysadmin role

 
USE master 
GO

--Add Windows logins to SQL Server 
IF NOT EXISTS (SELECT FROM sys.syslogins WHERE name 'SQLSRV90\SQLUser1'
   
CREATE LOGIN [SQLSRV90\SQLUser1] 
   
FROM WINDOWS 
   
WITH DEFAULT_DATABASE AdventureWorks 

IF NOT EXISTS (SELECT FROM sys.syslogins WHERE name 'SQLSRV90\SQLDBA'
   
CREATE LOGIN [SQLSRV90\SQLDBA] 
   
FROM WINDOWS 
   
WITH DEFAULT_DATABASE AdventureWorks 

Next, we will add those logins as users in the AdventureWorks database

USE AdventureWorks

--Add the new logins to the AdventureWorks database 
CREATE  USER SQLUser1 FOR  LOGIN [SQLSRV90\SQLUser1] 
CREATE  USER  SQLDBA FOR  LOGIN [SQLSRV90\SQLDBA]

--Add SQLDBA Windows account to the db_owner role 
EXEC sp_addrolemember 'db_owner''SQLDBA' 
GO 

Then, let's create a stored procedure that encapsulates the logic we presented earlier. For demonstration purposes, we will only do a SELECT command so we only need to grant SELECT permissions on the SQLUser1 user.

--Create procedure that executes a SELECT with a BACKUP DATABASE command 
CREATE PROCEDURE dbo.DisplayContextwithRevert 
WITH EXECUTE AS CALLER 
AS 
--The user will only be granted permission to do this section of the code 
SELECT FROM Person.Contact 
--We will just display the execution context of the user executing this section of the code for demonstration 
SELECT CURRENT_USER AS UserName

--We will switch execution context to a more privileged user to do this portion of the code 
EXECUTE AS USER='SQLDBA'
BACKUP DATABASE AdventureWorks TO DISK='C:\AdventureWorks.BAK' WITH INITSTATS=10;
--We will just display the execution context of the user executing this section of the code 
SELECT CURRENT_USER AS UserName

--We will revert to the execution context of the original caller to limit the privileges back
REVERT
SELECT FROM Person.Contact 
SELECT CURRENT_USER AS UserName
GO

Now, we proceed to grant only the appropriate permissions to the database user, SQLUser1. Note that SQLUser1 does not have privileges to do a database backup as the user is not a member of the dbo nor the db_backupoperator role. But since he needs to do a database backup from within the stored procedure, he needs to switch context to a more privileged user, SQLDBA. We, then, grant him IMPERSONATE permissions to impersonate SQLDBA

-- Grant user permissions 
GRANT EXECUTE ON dbo.DisplayContextwithRevert TO SQLUser1
GRANT SELECT ON Person.Contact TO SQLUser1 
GO 

-- Grant the IMPERSONATE  permission on the SQLUser1 user so it can switch execution context to SQLDBA 
GRANT IMPERSONATE ON USER:: SQLDBA TO SQLUser1
 

Testing the stored procedure

Let's login to SQL Server using SQLUser1 and run a BACKUP DATABASE command.


Notice that SQLUser1 does not have explicit permission to run a BACKUP DATABASE command. Running the stored procedure dbo.DisplayContextwithRevert would allow SQLUser1 to run the BACKUP DATABASE command with elevated privileges as SQLDBA and revert back to being SQLUser1. The highlighted CURRENT_USER variable values illustrate the execution context while running the different code blocks from inside the stored procedure

 

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





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     



Monday, June 22, 2015 - 11:38:41 AM - Armando Prato Back To Top

Stumbled across this while searching on a related topic and perhaps this comment will help someone else.

sa is a login.   You can impersonate the sa login i.e. GRANT IMPERSONATE ON LOGIN::sa to <your user>

The usual caveats apply,  sa is too much privilege for a user and YMMV.


Friday, September 27, 2013 - 12:01:20 AM - steve Back To Top

"We'll first add logins to SQL Server. One will be an ordinary user with very minimal privileges and another would be a member of the sysadmin role "

 

You never granted either login the sysadmin role? 

 

I need to impersonate SA to execute the RECONFIGURE cmd in a store procedure.  The database owner is SA.  I've tried using With Execute AS OWNER and With Execute as USER = 'dbo', both fail and pretty much say you can't impersonate SA.  Can this be done and can you show an example granting SA or sysadmin role?

 

 

 

ALTER

Procedure[dbo].[usp_BCP_EXPORT_Table_Data_to_Text_File]

 

(

 

 

@Drive_and_Path

Varchar(200),

 

@FileName

Varchar(50),

 

@DatabaseName

Varchar(50),

 

@SchemaName

Varchar(50),

 

@TableName

Varchar(50)

 

)

 

 

-----------------------------------------------------------------------------------------------------------------------

 

--

 

-- Purpose: This procedure allows you to export data from a table or view into a non delimited text file.

 

--

 

-- Modification Log:

 

--

 

-- 1. Steve 07/18/2012 Created.

 

--

 

-- Sample Exec:

 

/*

 

exec usp_BCP_EXPORT_Table_Data_to_Text_File

 

@Drive_and_Path = 'F:\SSIS',

 

@FileName = 'bcp_export_test_file.txt',

 

@DatabaseName = 'SCO_Processing',

 

@SchemaName = 'dbo',

 

@TableName = 'vw_parsed_Data_by_PayType_CtrType'

 

 

 

 

 

 

*/

 

--

 

--

 

-----------------------------------------------------------------------------------------------------------------------

 

 

--- NOTE: Comment this out for Production Use

 

 

with

EXECUTEASOWNER

 

AS

 

 

declare

@sqlvarchar(4500)

 

 

 

--EXECUTE AS USER = 'OWNER'

 

 

 

 

-- enable xp_cmdshell

 

 

exec

sp_configure'show advanced options',1

 

RECONFIGURE

 

 

exec

sp_configure'xp_cmdshell',1

 

RECONFIGURE

 

 

 

 

 

 

-- create the BCP DOS command to export the data to a text file and execute it via the XP_CMDSHELL extended stored procedure

 

 

SET

@sql='bcp ['+@DatabaseName+'].['+@SchemaName+'].['+@TableName+'] out '+@Drive_and_Path+'\'+@FileName+' -c -T -S'+@@servername

 

 

 

exec

master..xp_cmdshell@sql

 

 

 

 

 

 

 

-- disable xp_cmdshell

 

 

exec

sp_configure'xp_cmdshell',0

 

RECONFIGURE

 

 

exec

sp_configure'show advanced options',0

 

RECONFIGURE

 

 

;

 

-----------------------------------------------------------------------------------------------------------------------

 

-- End of Procedure usp_BCP_EXPORT_Table_Data_to_Text_File

 

-----------------------------------------------------------------------------------------------------------------------o

 

 

 

 

 

 


Learn more about SQL Server tools