SA0166 : Avoid altering security within stored procedures

The topic describes the SA0166 analysis rule.

Message

Avoid altering security within stored procedures

Description

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.

How to fix

Review the stored procedure and consider refactoring it to remove the altering security code .

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
13 minutes per issue.
Categories

Design Rules, Security Rules

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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
Analysis Results
  Message Line Column
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
See Also

Other Resources

Analysis Rules