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