Home » Articles » General DBA » Context Switching or Database Impersonation using EXEC AS

Context Switching or Database Impersonation using EXEC AS

On the first execution, the table would not exist so the procedure will impersonate the database owner (shown in the image below) and create the table, assign INSERT and SELECT permissions and then revert back the execution context to original caller.

– Switching execution context to DBUser, to test the procedure

EXECUTEASUSER=’DBUser’;

EXECUTE dbo.LogError’Primary Key violation.’

– Reverting back the execution context

REVERT;

If the database user calls the procedure again or on subsequent execution, since this log table already exists it will log the information in the table and will complete the execution of the procedure and so does not impersonate the database owner as it does not require additional privileges.

Finally, the below script cleans up the objects created in this session.

– Clean up scripts

– Remove the created procedure and table

DROPPROCEDURE dbo.LogError;

DROPTABLE GlobalLog;

– Remove the temporary principals.

DROPUSER DBUser;

DROPUSER DBOwner;

DROPLOGIN DBUserLogin;

DROPLOGIN DBOwnerLogin;

Conclusion

I this article, I briefly discussed the SETUSER command (which was the only option prior to SQL Server 2000) to impersonate the database user, its limitations and how EXEC or EXECUTE AS clause overcomes these limitations. The new EXEC AS clause allows you to change the execution/security context of a session by explicitly specifying a login or user name in an EXECUTE AS statement or implicitly by specifying the EXECUTE AS clause in a module definition. You can use the REVERT statement to switch back the execution context to the caller of the last EXECUTE AS clause.

References

EXEC AS Clause

REVERT Statement

Understanding Execution Context

Understanding Context Switching

EXECUTE AS vs. SETUSER

SQL Server Books Online (BOL) / MSDN

ASK A QUESTION