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