This Friday tip is a fast small tip that could save some developers or even sqlserver administrators some time and give them more flexibility if they do not already know it. You all may have passed through a situation when you needed to create a sqlserver login with specific set of permissions. Moreover you must have used to test those permission after creating that user. Before sqlserver 2005 and even after it for a plenty of time I used to reconnect to sql server with that user to be able to test it.

Sqlserver 2005 came with a handy statement that allows you to change your execution context to a different user and then revert to your original login without leaving your query window.

Suppose you logged to your database as an administrator and created a user named TestMe who have a permission to select from a table named QueryMe .

To test this user, all you need to do is using the Execute As statement and then Revert to your original admin login when done like below.

Execute As user = 'TestMe'

Select * from QueryMe

Revert

Easy and Fast, Right ?