More things I learned today: EXEC AS USER on dynamic SQL

I really liked the addition of the WITH EXECUTE AS clause when defining stored procedures and functions, to change the execution context, just for the duration of the stored procedure or function. For example:

CREATE PROC SomeSchema.SomeProc

WITH EXECUTE AS USER = ‘Fred’

AS

… 

I’d also used the EXEC AS clause to temporarily change my execution context during testing. For example:

EXEC AS USER = ‘Fred’;

-- Try some code here while running as Fred

REVERT;

But what I didn’t realize is that you can set the execution context for a single execution like this:

EXEC (‘Some command to be executed as Fred’) AS USER = ‘Fred’;

Back in SQL 2000, I made a big effort to read all of Books Online. The more I read of the current BOL, the more I think I need to do that again. (Mind you, I’m not sure that that’s even possible any more. I think they’re writing/modifying it faster than I have time to read it).

2011-08-13