SA0166 : Avoid altering security within stored procedures
The topic describes the SA0166 analysis rule.
Avoid altering security within stored procedures
The rule checks and alerts for usage of GRANT, REVOKE, or DENY statements within the body of a stored procedure.
Avoid altering security within stored procedures, functions, and triggers. This can lead to unnecessary database calls, or it can hinder troubleshooting security permissions.
Review the stored procedure and consider refactoring it to remove the altering security code .
The rule has a Batch scope and is applied only on the SQL script.
Rule has no parameters.
The rule does not need Analysis Context or SQL Connection.
Design Rules, Security Rules
There is no additional info for this rule.
1CREATE PROCEDURE testsp_SA0200 ( 2 @Code VARCHAR(30) = NULL 3) 4AS 5BEGIN 6 IF @Code IS NULL 7 SELECT * FROM Table1 8 ELSE 9 SELECT * FROM Table1 WHERE Code like @Code + '%' 10 11 UPDATE MyTable SET Col1 = 'myvalue' 12 13 BEGIN TRAN 14 GRANT EXEC ON testsp_SA0200 to myuser 15 COMMIT TRAN 16 17 GRANT EXEC ON testsp_SA0200 to myuser --IGNORE:SA0166 18 19 REVOKE SELECT ON dbo.Table1 TO myuser 20 21 DENY EXECUTE ON testsp_SA0200 to myuser 22 23END 24 25-- this is ignored, because it is reported by SA0150 26GRANT EXEC ON testsp_SA0200 to myuser
|1||SA0166 : Avoid altering security within stored procedures.||14||8|
|2||SA0166 : Avoid altering security within stored procedures.||19||4|
|3||SA0166 : Avoid altering security within stored procedures.||21||4|