SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

EXECUTE AS USER = ‘Somebody else’

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 comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...