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;

GO

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

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |