DF072: A stored procedure or trigger executing dynamic SQL is used without the WITH EXECUTE AS clause.

Last modified: December 25, 2024

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

It is recommended to use the WITH EXECUTE AS clause for stored procedures or triggers executing dynamic SQL.

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
  EXEC sys.sp_executesql N'select suser_sname()'
END
GO
EXEC dbo.DemoExecDsql
GO

Compliant solution

CREATE OR ALTER PROCEDURE dbo.DemoExecDsql WITH EXECUTE AS OWNER
AS
BEGIN
  EXEC sys.sp_executesql N'select suser_sname()'
END
GO
EXEC dbo.DemoExecDsql
GO