Security in SQL Server 2005 – execute as…with cookie

You’ve probably heard by now of the usage of the EXECUTE AS clause with procedural code. As in “CREATE PROCEDURE foo WITH EXECUTE AS OWNER”. But EXECUTE AS can also be used at a session level,like this:

EXECUTE AS USER=’fred’
— some T-SQL here
REVERT

This is meant to replace the SETUSER verb because you might want things executed as a specfic login as well as user. So there’s also EXECUTE AS LOGIN=’freds_login’. You can aribtrarily nest EXECUTE AS blocks and there’s subject to the nice, granular, IMPERSONATE privilege. Originally, I’d heard there was going to be a “REVERT ALL”, but there’s security mischief that might happen with this, so it never happened.

BUT.
There are two variations of EXECUTE AS that are interesting, based on security considerations. First one is “EXECUTE AS USER=’fred’ WITH NO REVERT”. Exactly what it sounds like.

Next one is EXECUTE AS WITH COOKIE. The idea is similar to what I described in sp_unsetapprole. You don’t want just any ol’ level of impersonation to do REVERT. So EXECUTE..WITH COOKIE gives you a cookie to use with REVERT.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.