EXECUTE AS USER = ‘Somebody else’2
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.
Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: code language, microsoft sql server, security
2 thoughts on “EXECUTE AS USER = ‘Somebody else’”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] 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. […]
[…] use impersonation a lot. It’s a really easy way to check if someone has the permissions they are supposed to. That said, a co-worker recently had an interesting question. They were testing permissions on a […]