SQL Server impersonation, also known as context switching, is a powerful feature that allows the executing user to assume the permissions of another user or login until the context is set back, set to another user, or the session is ended. In this article, we will explore the concept of SQL Server impersonation and discuss two mechanisms for accomplishing this task.
SETUSER
In SQL Server 2000, the SETUSER statement is used for context switching. By executing this statement with a provided username, the execution permissions are temporarily set to that of the supplied account. Multiple calls can be made, and the context will continue to switch until it is reverted back to the original context by calling SETUSER without a username.
It’s important to note that an account wishing to call SETUSER must have the sysadmin server role. Contrary to Books Online, users with dbo rights cannot utilize this statement.
Here is an example of how context switching works using SETUSER:
SETUSER 'jdoe'
SELECT SUSER_SNAME()
SETUSER 'jschmoe'
SELECT SUSER_SNAME()
SETUSER
SELECT SUSER_SNAME()
The above example shows the context being switched from the logged-in account to a specific SQL login, then to another SQL user, and finally back to the logged-in account. The results will display the username of each context.
EXECUTE AS
Starting with SQL Server 2005, the EXECUTE AS statement has replaced SETUSER for context switching. There are two incarnations of EXECUTE AS: EXECUTE AS LOGIN and EXECUTE AS USER.
EXECUTE AS LOGIN allows the calling account to take on the server-level permissions of the login, such as securityadmin. On the other hand, EXECUTE AS USER provides access to the database-level permissions of the passed-in username.
Here is an example of using EXECUTE AS LOGIN:
EXECUTE AS LOGIN = 'securacct'
EXEC spCreateNewLogin 'jdoe'
REVERT
If you only need to switch the context at the database level, EXECUTE AS USER will suffice. This is useful when a system account needs to impersonate the user who initiated a transaction in a front-end application.
Here is an example of using EXECUTE AS USER:
EXECUTE AS USER = 'jdoe'
EXEC spSaveOrderItem 'B123', 1, '9484844'
REVERT
Permissions and Impersonation
SETUSER requires sysadmin permissions, while EXECUTE AS USER only requires dbo permissions to execute the statement. If you have a legitimate reason to impersonate another user or login, you can grant impersonation rights to the executing account using the GRANT IMPERSONATE statement.
Impersonation can also be used across linked servers. With SETUSER, a call that crosses linked servers will fail unless the context is reverted to the original user prior to crossing servers. However, with EXECUTE AS, crossing linked servers as an impersonated user or login becomes possible by ensuring that the principle (user/login) is recognized on the linked server and the linked servers are trustworthy.
Conclusion
SQL Server impersonation and context switching are powerful features that allow for flexible permission management. In SQL Server 2000, SETUSER is used for context switching, while SQL Server 2005 and above offer the improved EXECUTE AS statement. Understanding the requirements for impersonation at the server or database level will help determine the appropriate method to use. It’s important to consider the security implications and use GRANT IMPERSONATE when necessary.
For more information on the concepts covered in this article, refer to the following links:
Thank you for reading!