Accessing the calling context in modules that use EXECUTE AS

  • Article

In many occasions, marking a module (i.e. SP, trigger, etc.) with execute as can be really useful as it allows a controlled impersonation during the module execution; but at the same time there are many cases that it is necessary to access information using the caller's execution context (i.e. revert to the default behavior), for example, for ad-hoc auditing where the auditor keeps a table with information on who accessed the data.

For such cases there is a mechanism that allows to impersonate the calling context, effectively switching back and forth at will: EXECUTE AS CALLER.

EXECUTE AS CALLER will impersonate the calling context, but since we are just reverting to the original module calling convention (executing under the caller's context), there are no permission checks for this call.

In this case I think a quick example will be worth a lot more than an in-depth technical discussion, so I will jump directly to the demo, but feel free to ask any technical questions in the comments.

-- Create a application-specific user

-- The SP will impersonate this context

-- as it has access to the necessary resources

--

CREATE USER [ApplicationUser] WITHOUT LOGIN

go

 

-- Create a schema for the application

-- In this example, the application-specific user

-- will also be the owner of the schema

--

CREATE SCHEMA [ApplicationSchema] AUTHORIZATION [ApplicationUser]

go

 

-- SP that will run under the impersonated context

-- and then revert to the caller

--

CREATE PROC [ApplicationSchema].[Demo_1]

    WITH EXECUTE AS 'ApplicationUser'

AS

    -- Verify the context for the module,

    -- Should be "ApplicationUser"

      SELECT user_name() as 'Impersonated context',

           user_id() as 'UserId';

     

    -- At this point, we want to do some

    -- operation as the calling context,

    -- for example, ad-hoc auditing

    -- So I will revert to the caller

    --

    EXECUTE AS CALLER;

      -- Verify the context

    --

    SELECT user_name() as 'Impersonated context',

           user_id() as 'UserId';

    -- After permorming the actions under the caller's context,

    -- go back to the application-impersonated context

    REVERT;

 

    -- Verify the context for the module once again,

    -- Should be back to "ApplicationUser"

      SELECT user_name() as 'Impersonated context',

           user_id() as 'UserId';

go

 

-- SP that will run under the impersonated context

-- And then view the original session login

--

CREATE PROC [ApplicationSchema].[Demo_2]

    WITH EXECUTE AS 'ApplicationUser'

AS

    -- Verify the context for the module,

    -- Should be "ApplicationUser"

      SELECT original_login() as 'original_login',

        user_name() as 'Impersonated context',

        user_id() as 'UserId';

go

 

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

-- Testing

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

CREATE USER [RegularUser] WITHOUT LOGIN

go

 

-- Grant EXECUTE on all modules on the app schema

-- to this test user

GRANT EXECUTE ON SCHEMA::[ApplicationSchema] TO [RegularUser]

go

 

-- Impersonate this low-priv user and test both SPs

--

EXECUTE AS USER = 'RegularUser'

go

 

-- Expected output:

-- Impersonated context | UserId

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

-- ApplicationUser | x

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

-- RegularUser | y

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

-- ApplicationUser | x

--

EXEC [ApplicationSchema].[Demo_1]

go

 

-- original_login | Impersonated context | UserId

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

-- Original session login | ApplicationUser | x

--

-- NOTE: The

--

EXEC [ApplicationSchema].[Demo_2]

go

 

-- Current user name | UserId

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

-- RegularUser | y

--

SELECT user_name() as 'Current user name',