Context Switching or Database Impersonation using EXEC AS
default, when a user establishes a connection to SQL server, a session starts
and ends when the user disconnects from SQL Server. All operations and activities
during an established session are performed under the security context of the logged
on user; it means SQL Server checks permission of the user to perform any
operation. So how allow a user to perform high privileged activities without allowing
the user to have high privilege access?
Server 2005/2008 provides the ability to change the execution/security context
with the EXEC or EXECUTE AS clause. You can explicitly change the execution
context by specifying a login or user name in an EXECUTE AS statement for batch
execution or by specifying the EXECUTE AS clause in a module (stored procedure,
triggers and user-defined functions) definition. Once the execution context is
switched to another login or user name, SQL Server verifies the permission
against the specified login or user (specified with EXECUTE AS statement) for
subsequent execution instead of the execution context of current user. In other
words, the SQL Server login or database user name will be impersonated for the
remainder of the session or until the execution context is reverted, or the module
completes execution or the session is disconnected. This enables DBAs to allow
users with limited privileges to change the execution context to high
privileged accounts only when required and switch back to the original
execution context with limited privileges once the high privileged operations
are completed. The REVERT
clause to switches back the execution context to the caller of the last EXECUTE
The EXEC AS
There are five types of database impersonation (context
switch) that can be performed using EXEC AS:
This is the default behavior for all types of modules except Service Broker queues.
Under this option, all the statements inside the module are executed under the
execution context of the user executing the module. As a result, the caller
must have appropriate permission on the module as well as on all the objects
being referenced by that module.
– With this option, all the statements inside the module are executed under the
execution context of the creator of the module or the owner of the module. If the
module does not belong to an owner then in that case the execution context of
the schema owner of the module will be used. One point to note is the owner
must be an individual user, not a Windows group or database role. You cannot
use EXECUTE AS OWNER for DDL triggers with server scope or logon triggers in
which case EXECUTE AS login_name should be used.
option is similar to use of EXECUTE AS user_name, where the specified
user is the person who has created or modified the module. The person who
modifies the module might not be necessarily the same as its owner. In other
words it designates the user who creates has last modified the module definition
as the one whose security context will be used to execute it. For Service
Broker queues, EXECUTE AS SELF is the default option.
this option, the execution context of a particular or specified database user
is used for the duration of module execution or until revert statement.This
means that permissions on all referenced objects by the module are verified
against the specifieduser_name, rather than against the original caller
of the module. user_name must exist in the current database and must be
a singleton account, not a Windows group or database role. This option willalso
not work with DDL triggers with server scope or logon triggers; rather you need
to use EXECUTE ASlogin_nameoption as discussed below.
One point to note is
that by default the scope of using EXECUTE AS user_name applies at
database level only after impersonation (context switch) – if you refer an
object outside the scope of the database, it will throw an error.
– With this option the execution context of a specified SQL Server login is
used for the duration of module execution or until revert. This means that
permissions on all referenced objects by the module are verified against the
specified login_name, rather than against the original caller of the
module. login_name cannot be a group, role, certificate, key, or
built-in account. Unlike the user_name option which has database scope,
the login_name is server scoped and can only be specified for DDL
triggers with server scope or logon triggers.
I said, EXECUTE AS login_name has server wide scope it means after the
context switch, the impersonated user can access all server resources which login_name
has access on.
No matter what option is being used inside a module
definition, the caller of the module must have EXECUTE permission on the
The SQL Server database engine first verifies that the
caller of the module has EXECUTE permission or not, if yes then:
the module has EXECUTE AS CALLER, which is the default option, the additional
permissions are also checked against the caller of the module for the objects
which are being accessed by the module.
the module has an option other than EXECUTE AS CALLER, then the additional
permission checks for objects which are being accessed by the module are
performed against the login or user account specified with the EXECUTE AS
clause. In effect, the caller of the module is impersonating the specified login