Changing the security context of a session in SQL Server
Today I was testing Row Level Security in a database and I needed to execute the same SQL query with different users to see how it impacted the execution plan and the results.
Using EXECUTE AS USER:
EXECUTE AS USER = 'the_user_name';
-- Check that we are impersonating the right user
SELECT SUSER_NAME(), USER_NAME();
-- The actual SQL query goes here
REVERT keyword at the end is important because otherwise the user impersonation will persist for the duration of the session.
This user was missing the
SHOWPLAN permission to show the actual query execution plan. The permission can be granted simply with:
GRANT SHOWPLAN TO the_user_name