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?
Assume that you have two users ( SQLDBA, SQLUser); SQLDBA is having the db_owner role to be able to take backup of database where as SQLUser is having only the SELECT/EXECUTE permission on a stored procedure (usp_Test). (i.e. we are sure that SQLUser do not have permission to take backup of database).
CREATE PROC dbo.usp_Test
WITH EXECUTE AS CALLER
AS
--The user will only be granted permission to do this section of the code
SELECT * FROM Person.Contact
BACKUP DATABASE AdventureWorks TO DISK='C:\AdventureWorks.BAK' WITH INIT, STATS=10 ;
SELECT * FROM Person.Contact
GO
Now what if you run the above SP using
SQLUser ??
obviously, you will get the error like "
BACKUP DATABASE Permission denied on the database AdventureWorks "
To fix the above issue, you can make use of EXECUTE AS and REVERT statements in SQL Server.
CREATE PROC dbo.usp_Test 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 INIT, STATS=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