THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

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




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


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).

Published Saturday, August 13, 2011 7:14 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Shatrughna Kumar said:

Nice post.

August 13, 2011 7:48 AM

userSql said:

Thanks a lot dude, it really helped me with my web:

October 22, 2015 7:18 AM

Leave a Comment


This Blog



No tags have been created or used yet.


Privacy Statement