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
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