T-SQL – How to execute as another user

If you are ever unfortunate enough to be trying to give someone sql permissions and its not quite working for you and you have already got them to attempt it a couple of times with no success…. you can use this handy code to run a statement as a user from your own query window. Once you have it working you can say “ok its going to work now”, and be assured that it actually will work!

EXECUTE AS USER = 'domain\some.user';

-- Now give it a go

begin tran
update <yourtable>
set somefield =1 
where somefield = '2' 
commit --rollback
REVERT; -- Sets it back to your own user

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

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