THE SQL Server Blog Spot on the Web

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

The Bit Bucket (Greg Low): 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

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

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

Comments

 

Shatrughna Kumar said:

Nice post.

August 13, 2011 7:48 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement