EXECUTE AS USER = ‘Somebody else’

1

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.

One thought on “EXECUTE AS USER = ‘Somebody else’

  1. […] Impersonation is a highly useful tool in your toolbox. With the EXECUTE AS clause you can allow your stored procedure to do things that the user doesn’t have permission to. EXECUTE AS LOGIN/USER gives you the ability to pretend you are someone else test/view their permissions. I frequently use this technique to solve permissions problems. […]

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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

Join 2,470 other followers

Follow me on Twitter

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