Vincent Costel

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

The 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