SA0129 : Use WITH EXECUTE AS clause for stored procedures executing dynamic SQL

Introduction

Stored procedures with dynamic SQL and no EXECUTE AS clause pose security risks.

Description

In SQL Server, stored procedures can include dynamic SQL that executes queries based on parameters or conditions. Without specifying an EXECUTE AS clause, there is a risk that the dynamic SQL will run under the caller’s security context, potentially exposing sensitive data or allowing unauthorized operations.

For example:

-- Example of a stored procedure with dynamic SQL
CREATE PROCEDURE GetUserData
AS
BEGIN
    DECLARE @sqlCommand NVARCHAR(1000);
    SET @sqlCommand = 'SELECT * FROM Users WHERE UserId = 1';
    EXEC sp_executesql @sqlCommand;
END

This example is problematic because:

The procedure executes dynamic SQL without any specific security context, risking unwanted data access.
Without EXECUTE AS , it depends on the default permissions of the caller, which can lead to privilege escalation.

How to fix

Use the EXECUTE AS clause to ensure dynamic SQL in stored procedures executes in a specified security context, mitigating security risks.

Follow these steps to address the issue:

1.Determine the appropriate security context for the dynamic SQL execution. This could be CALLER , SELF , or a specific user.

2.Modify the stored procedure to include the EXECUTE AS clause. For example, add WITH EXECUTE AS OWNER or another relevant user context.

3.Recreate or alter the stored procedure to include the changes.

For example:

CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS OWNER
AS
    SET NOCOUNT ON;
    EXEC ('SELECT v.Name AS Vendor, p.Name AS "Product name",
      v.CreditRating AS "Rating",
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v
    INNER JOIN Purchasing.ProductVendor pv
      ON v.BusinessEntityID = pv.BusinessEntityID
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID
    ORDER BY v.Name ASC');

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Name	Description	Default Value
IgnoreExecDynamicSqlAfterExecuteAsStatement	The parameter specifies whether ot not to ignore dynamic SQL statements which are preceded by EXECUTE USER/LOGIN statemetns.	no
IgnoreExecDynamicSqlHavingAsUserClause	The parameter specifies whether ot not to ignore dynamic SQL statements which have explicit AS USER/LOGIN clause.	no

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

EXECUTE AS (Transact-SQL)

CREATE PROCEDURE (Transact-SQL)

Example Test SQL

ALTER PROCEDURE mysp_Test_SA0129
-- WITH EXECUTE AS OWNER
AS


DECLARE @TableName varchar(100)
SET @TableName = 'ProductModel'

EXEC ( 'SELECT * FORM Production.ProductModel') AS login = 'login-name'

EXEC sp_executesql 'SELECT * FORM Production.ProductModel' -- IGNORE:SA0129

EXECUTE AS USER = 'user2';

EXECUTE ('SELECT * FORM Production.' + @TableName)

EXEC sp_executesql 'SELECT * FORM Production.ProductModel'

EXEC sys.sp_executesql 'SELECT * FORM Production.ProductModel'

declare @result int
EXEC @result = master.sys.sp_executesql 'SELECT * FORM Production.ProductModel'

Analysis Results

	Message	Line	Column
1	SA0129 : Use WITH EXECUTE AS clause for stored procedures executing dynamic SQL.	1	0

Analysis Rules