Click or drag to resize

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.

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

SQL
 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
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0129

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.

Categories

Security Rules

Additional Information
Example Test Script
SQL
 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

 MessageLineColumn
1SA0129 : Use WITH EXECUTE AS clause for stored procedures executing dynamic SQL.10
See Also

Other Resources