Click or drag to resize

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.

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.

Categories

Design Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 1CREATE PROCEDURE testsp_SA0200 (
 2        @Code VARCHAR(30) = NULL
 3)
 4AS
 5
 6BEGIN
 7    IF @Code IS NULL
 8        SELECT * FROM Table1
 9    ELSE
10        SELECT * FROM Table1 WHERE Code like @Code + '%'
11
12    UPDATE MyTable SET Col1 = 'myvalue'
13
14    BEGIN TRAN
15        GRANT EXEC ON testsp_SA0200 to myuser
16    COMMIT TRAN
17
18    GRANT EXEC ON testsp_SA0200 to myuser  --IGNORE:SA0166
19
20    REVOKE SELECT ON dbo.Table1 TO myuser
21
22    DENY EXECUTE ON testsp_SA0200 to myuser
23
24END
25
26-- this is fine because it is outside of the stored procedure
27GRANT EXEC ON testsp_SA0200 to myuser

Analysis Results

 MessageLineColumn
1SA0166 : Avoid altering security within stored procedures.158
2SA0166 : Avoid altering security within stored procedures.204
3SA0166 : Avoid altering security within stored procedures.224
See Also

Other Resources