DF072: A stored procedure or trigger executing dynamic SQL is used without the WITH EXECUTE AS clause.
Last modified: June 12, 2025
The topic describes the DF072 T-SQL code analysis rule.
Category
EXECUTION RULES
Message
A stored procedure or trigger executing dynamic SQL is used without the WITH EXECUTE AS
clause.
Description
When a stored procedure or trigger runs dynamic SQL, it’s recommended to include the WITH EXECUTE AS
clause. This clause explicitly defines the execution context and helps prevent permission-related errors and potential security issues caused by implicit context resolution.
Additional information
Without the WITH EXECUTE AS
clause, the dynamic SQL executes within the context of the caller’s permissions. This can potentially expose sensitive data or allow unauthorized actions if the caller has elevated privileges.
Additionally, code executing dynamic SQL without the WITH EXECUTE AS
clause can be harder to maintain and audit. It may not be immediately clear who has permissions to execute the dynamic SQL or what actions it performs.
Noncompliant code example
CREATE OR ALTER PROCEDURE dbo.DemoExecDsql
AS
BEGIN
DECLARE @result INT
EXEC @result = sys.sp_executesql @stmt = N'select suser_sname()'
RETURN @@error;
END
GO
DECLARE @result INT
EXEC @result = dbo.DemoExecDsql
GO
Compliant solution
CREATE OR ALTER PROCEDURE dbo.DemoExecDsql
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @result INT
EXEC @result = sys.sp_executesql @stmt = N'select suser_sname()'
END
GO
DECLARE @result INT
EXEC @result = dbo.DemoExecDsql
GO