Monday, October 27, 2014

How To Identify Sessions With Context Switching In SQL Server??

Context switching is the act of executing T-SQL code under the guise of another user connection, in order to utilize their credentials and level of rights.

By default, a session starts when a user logs in and ends when the user logs off. All operations during a session are subject to permission checks against that user. When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. After the context switch, permissions are checked against the login and user security tokens for that account instead of the person calling the EXECUTE AS statement.  

We can use our DMV "sys.dm_exec_sessions" To identify the sessions with context switching as below:

SELECT session_id , login_name , original_login_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND login_name <> original_login_name

To explain this I have a sample database TestVK. I have logged in with credentials of "vimal". But Now I want to access the rights of user "sa1". T-SQL code for same are as below:

 EXECUTE AS LOGIN ='SA1';
 SELECT TOP 1 * FROM Item_Name



 Now lets execute the DMV "sys.dm_exec_sessions" to see the context switching sessions:

SELECT session_id , login_name , original_login_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND login_name <> original_login_name

Output:
session_id login_name original_login_name
80             sa1             vimal



Reference: Microsoft BOL, Red Gate


Thanks For Reading this Post!!!

No comments:

Post a Comment