CR0002: Avoid altering security within stored procedures

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.

Author

Jeff Foushee

Example

CREATE PROCEDURE testsp_CR0002 (
    @Code VARCHAR(30) = NULL
)
AS

BEGIN
    IF @Code IS NULL
        SELECT * FROM Table1
    ELSE
        SELECT * FROM Table1 WHERE Code like @Code + '%'

    UPDATE MyTable SET Col1 = 'myvalue'

    BEGIN TRAN
        GRANT EXEC ON testsp_CR0002 to myuser
    COMMIT TRAN

    GRANT EXEC ON testsp_CR0002 to myuser  --IGNORE:CR0002

    REVOKE SELECT ON dbo.Table1 TO myuser

    DENY EXECUTE ON testsp_CR0002 to myuser

END

-- this is fine because it is outside of the stored procedure
GRANT EXEC ON testsp_CR0002 to myuser  

[av_promobox button=‘yes’ label=‘Download’ link=‘manually,https://sqlenlight.com/downloads/cr0002.zip’ link_target=” color=‘theme-color’ custom_bg=‘#444444’ custom_font=‘#ffffff’ size=‘large’ icon_select=‘yes’ icon=‘ue82d’ font=‘entypo-fontello’ box_color=” box_custom_font=‘#ffffff’ box_custom_bg=‘#444444’ box_custom_border=‘#333333’ av_uid=‘av-2zrx3z’] Download and try the CR0002 analysis rule. [/av_promobox]