Use EXECUTE AS CALLER stand-alone statement

For example, assume the following conditions:

group has access to the

database.

is a member of

and therefore has access to the

database.

The user that is creating or altering the module has permissions to create principals.

When the following

statement is run, the

is

implicitly created as a database principal in the

database.

Use the

stand-alone statement inside a module to set the execution context

to the caller of the module.

Assume the following stored procedure is called by.

Windows domain account that is specified in the

clause. This will cause the

execution of the module to fail.

CompanyDomain\SQLUsers

Sales

CompanyDomain\SqlUser1

SQLUsers

Sales

CREATE PROCEDURE

CompanyDomain\SqlUser1

Sales

EXECUTE AS CALLER

SqlUser2

EXECUTE AS

USE
Sales;
GO
CREATE
PROCEDURE dbo.usp_Demo
WITH
EXECUTE
AS
'CompanyDomain\SqlUser1'
AS
SELECT
USER_NAME();
GO

CREATE
PROCEDURE dbo.usp_Demo
WITH
EXECUTE
AS
'SqlUser1'
AS
SELECT
USER_NAME();
-- Shows execution context is set to SqlUser1.
EXECUTE
AS
CALLER;
SELECT
USER_NAME();
-- Shows execution context is set to SqlUser2, the caller of the module.
REVERT;