Technical FAQs

Ask a Question

How to execute SQL commands as a different Login

Issue
How to impersonate a user or login.

Product Line
Power Monitoring Expert (PME)
SQL Server

Environment
SQL Server Management Studio (SSMS)

Cause
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. In essence, the user or login account is impersonated for the duration of the session or module execution.

Resolution
The syntax is:
EXECUTE AS { LOGIN | USER } = '<name>';
note: The context to be impersonated can be either a 'LOGIN' or a 'USER', and 'name' is a valid login or user.

The change in execution context remains in effect until one of the following occurs:
- Another EXECUTE AS statement is run.
- The session is dropped.
- A REVERT statement is issued.

In the example below, it is determined if two different logins have 'sysadmin' server role or not. The login is changed with an 'EXECUTE AS statement.

EXECUTE AS login = 'ION'
IF IS_SRVROLEMEMBER ('sysadmin') = 1  
   print 'Current user''s login is a member of the sysadmin role'  
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0  
   print 'Current user''s login is NOT a member of the sysadmin role'  
REVERT;

EXECUTE AS login = 'REPORT'
IF IS_SRVROLEMEMBER ('sysadmin') = 1  
   print 'Current user''s login is a member of the sysadmin role'  
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0  
   print 'Current user''s login is NOT a member of the sysadmin role'  
REVERT;


          
 
Was this helpful?
What can we do to improve the information ?