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

The topic describes the SA0129 analysis rule.

Message

Use WITH EXECUTE AS clause for stored procedures executing dynamic SQL

Description

The rule checks for stored procedures which do not have EXECUTE AS clause specified and in the same time have dynamic SQL executed in the procedures body using EXECUTE statement or sp_executeSQL.

How to fix

Use the EXECUTE AS clause to ensure the dynamic SQL code inside the procedure is executed in the expected context.

SQL
Copy
 1CREATE PROCEDURE Purchasing.uspVendorAllInfo
 2WITH EXECUTE AS CALLER
 3AS
 4    SET NOCOUNT ON;
 5    EXEC ('SELECT v.Name AS Vendor, p.Name AS 'Product name', 
 6      v.CreditRating AS 'Rating', 
 7      v.ActiveFlag AS Availability
 8    FROM Purchasing.Vendor v 
 9    INNER JOIN Purchasing.ProductVendor pv
10      ON v.BusinessEntityID = pv.BusinessEntityID 
11    INNER JOIN Production.Product p
12      ON pv.ProductID = p.ProductID 
13    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.
Categories

Security Rules

Additional Information
Example Test SQL
SQL
Copy
 1ALTER PROCEDURE mysp_Test_SA0129
 2-- WITH EXECUTE AS OWNER
 3AS
 4
 5
 6DECLARE @TableName varchar(100)
 7SET @TableName = 'ProductModel'
 8
 9EXEC ( 'SELECT * FORM Production.ProductModel') AS login = 'login-name'
10
11EXEC sp_executesql 'SELECT * FORM Production.ProductModel' -- IGNORE:SA0129
12
13EXECUTE AS USER = 'user2';
14
15EXECUTE ('SELECT * FORM Production.' + @TableName)
16
17EXEC sp_executesql 'SELECT * FORM Production.ProductModel'
18
19EXEC sys.sp_executesql 'SELECT * FORM Production.ProductModel'
20
21declare @result int
22EXEC @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
See Also

Other Resources

Analysis Rules