EXECUTE AS USER = ‘Somebody else’

Leave a comment

September 9, 2012 by Kenneth Fisher

As a database administrator I get lots of security requests. “Please grant me read authority on this table, write on that table, and the ability to juggle.” (In case anyone is interested the last can be granted by adding the user to the db_juggling role.)  I’m going to guess a few of you have had similar requests.  Here’s where I run into problems.  I have a user who wants, for example, read/write access to a table.  So I do the grant using a role.  I tell the user I’m done.  He then returns to me letting me know he does not in fact have access.  So I double-check.  I fiddle, I might grant him access directly instead of using the role.  I ask him to try again.  He still doesn’t have access.  This might go back and forth half a dozen times.  It aggravates me, aggravates my user, and makes me look bad.  Now if he wants the permissions for a SQL login I could always ask for the password, log in as that id and test things out.  This will work, but of course getting someone else’s password, even when I’m a sysadmin on the server, isn’t exactly the best idea in the world. Not to mention that it won’t work if they are using a windows authenticated login.  However there is a better solution!  We have the technology!

EXECUTE AS USER = ‘Name’ will cause the current session to impersonate “Name” as long as “Name” is a database principal.  You can also use EXECUTE AS LOGIN = ‘Name’ for server principals.  By using “EXECUTE AS” I no longer run into problem I mentioned above.  The user places a request for permissions, I grant the permissions, I test the permissions and if I notice a problem I fix it and retest.  This time when I notify the user everything works perfectly.  He’s happy, I’m happy and I look good to my user.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,163 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: