SA0212 : The SETUSER is deprecated. It is recommended to use EXECUTE AS instead
Introduction
Section titled “Introduction”The SETUSER statement in T-SQL code is problematic because it is primarily maintained for backward compatibility and might be removed in future SQL Server releases.
Description
Section titled “Description”The SETUSER statement was traditionally used to impersonate another user within a SQL Server session. However, reliance on this statement can compromise the stability and future compatibility of applications, as it poses significant risks if it becomes unsupported in newer SQL Server versions.
For example:
-- Example of deprecated usageSETUSER 'other_user';-- Subsequent operations are executed as 'other_user'SELECT * FROM SensitiveTable;Using SETUSER can lead to code that becomes outdated and potentially non-functional as SQL Server evolves. It also introduces security concerns since it allows session user context switching without adequate accountability.
-
The code may fail in future versions of SQL Server where
SETUSERis no longer supported. -
It can create security vulnerabilities by allowing unmonitored access to sensitive data using impersonation.
How to fix
Section titled “How to fix”Replace the deprecated SETUSER statement with EXECUTE AS to ensure future compatibility and enhance security.
Follow these steps to address the issue:
1.Identify each instance of the SETUSER statement in your T-SQL code.
2.Replace the SETUSER 'other_user'; statement with EXECUTE AS USER = 'other_user'; .
3.Ensure that the user context is reverted using REVERT; after the necessary operations are completed to maintain security and data integrity.
For example:
-- Corrected usage of EXECUTE ASEXECUTE AS USER = 'other_user';SELECT * FROM SensitiveTable;REVERT;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”1 hour per issue.
Categories
Section titled “Categories”Deprecated Features, Bugs
Additional Information
Section titled “Additional Information”Deprecated Database Engine Features in SQL Server 2017
Example Test SQL
Section titled “Example Test SQL”SETUSER 'mary';
GRANT SELECT ON computer_types TO joe;
SETUSER;Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0212 : The SETUSER is deprecated. It is recommended to use EXECUTE AS instead. | 1 | 0 |
| 2 | SA0212 : The SETUSER is deprecated. It is recommended to use EXECUTE AS instead. | 5 | 0 |