Execute As in TSQL

Execute As

By default if you login into SQL Server a session gets started when login and session ends at logoff. What ever the transactions / operations we performed those are in specific to permissions against the login.

Using this EXECUTE AS option we can change the context of the session switched from current login to different login.

For Example you login into SQL Instance using “sa” login.

EXECUTE AS USER = 'TestUser'
SELECT USER_NAME()

SELECT * FROM category

SELECT * FROM fn_my_permissions (NULL, 'DATABASE'); -- List all the permissions for the TestUser has on the current database.
       
REVERT – Switch back to original login session ie., (“sa”) in this case.

SELECT USER_NAME(), SUSER_SNAME()

Note : Best practice is specify a login / user name which has least privileges.