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.
