SA0150 : The procedure grants permissions at the end of its body. Possible missing GO batch separator command

This problem arises when stored procedures inadvertently include GRANT or REVOKE permissions within their bodies. This typically happens if a procedure’s script removes the GO command that separates the main procedure from permission operations due to a misconception that the procedure body is limited to its BEGIN/END block.

For example:

1CREATE PROCEDURE ExampleProcedure
2AS
3BEGIN
4    -- Procedure logic here
5END
6
7GRANT SELECT ON SomeTable TO SomeUser;

In this case, the permission statement is executed every time the procedure runs. This can be a problem, especially if there is no explicit RETURN statement, leading to unintended security changes.

• It can alter security permissions unintentionally, affecting the integrity of database access control.

• It can degrade performance due to the additional overhead of executing permission statements repeatedly.

Ensure stored procedures do not unintentionally include permission changes in their execution to maintain security and performance.

Follow these steps to address the issue:

1. Review the stored procedure and identify any GRANT or REVOKE statements present within the procedure body after the END block.

2. Separate these permission statements from the procedure body using the GO command to ensure they are not executed every time the stored procedure runs.

3. Insert a GO command after the END statement of the procedure.

4. Place the permission statements (e.g., GRANT SELECT ON SomeTable TO SomeUser) after the GO command to execute them separately from the procedure logic.

For example:

1CREATE PROCEDURE ExampleProcedure
2AS
3BEGIN
4    -- Procedure logic here
5END
6GO
7GRANT SELECT ON SomeTable TO SomeUser;

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

The rule does not need Analysis Context or SQL Connection.

Design Rules, Security Rules

There is no additional info for this rule.

Other Resources