Using the EXECUTE AS statement to overcome security

If you want a given user to execute a sql server stored procedure, but that user doesn't have sufficient security rights to fully perform the steps executed in the stored procedure, and you don't want to grant the user more security than they need, you can use the "execute as" command.

Option 1 - You can execute an entire stored procedure under a different security context:

CREATE PROCEDURE dbo.usp_Demo

WITH EXECUTE AS 'CompanyDomain\SqlUser1' AS

Option 2 - You can execute a single step in a stored procedure under a different security context:

CREATE PROCEDURE dbo.usp_Demo WITH EXECUTE AS 'SqlUser1' AS

EXECUTE AS CALLER; (or EXECUTE AS USER='Domain\UserID')

SELECT user_name(); -- Shows execution context is set to SqlUser2, the caller of the module.

REVERT;