Accessing the calling context in modules that use EXECUTE AS

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.