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

Introduction

Procedures mistakenly modifying permissions can cause security and execution issues.

Description

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:

CREATE PROCEDURE ExampleProcedure
AS
BEGIN
    -- Procedure logic here
END

GRANT 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.

How to fix

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:

CREATE PROCEDURE ExampleProcedure
AS
BEGIN
    -- Procedure logic here
END
GO
GRANT SELECT ON SomeTable TO SomeUser;

Scope

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

Parameters

Name	Description	Default Value
OnTarget	The target of on which the permissions are granted or revoked.	Any

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

ALTER PROCEDURE dbo.FooGetTableA
    (
    @Parameter varchar(4)
    )
AS
BEGIN

    SELECT Column1
    FROM dbo.TableA
    WHERE Column2 = @Parameter

    GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole -- ignored as it is in the main BEGIN/END block.
END

-- GO
REVOKE EXEC ON dbo.FooGetTableB TO ApplicationRole
GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole

Analysis Results

	Message	Line	Column
1	SA0150 : Possible missing GO command. The procedure FooGetTableA grants/revokes permissions.	16	0
2	SA0150 : Possible missing GO command. The procedure FooGetTableA grants/revokes its own permissions.	17	0

Analysis Rules