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

The topic describes the SA0150 analysis rule.

Message

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

Description

The rule checks T-SQL code for stored procedures that are granting or revoking permissions at the end of their bodies.

This condition may occur when the procedure is scripted with permissions and the GO command separating the procedure body with the permission statements is removed due to the wrong belief that the procedure body is only inside the BEGIN/END block. In this way, the permission statements may be executed every time with the procedure, especially when there is no explicit RETURN statement.

The rule has a parameter – OnTarget, which specifies whether to check r only for the procedure’s own permissions, or for permissions of any database object.

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.

Categories

Design Rules, Security Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1ALTER PROCEDURE dbo.FooGetTableA
 2    (
 3    @Parameter varchar(4)
 4    )
 5AS
 6BEGIN
 7
 8    SELECT Column1 
 9    FROM dbo.TableA
10    WHERE Column2 = @Parameter
11
12    GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole -- ignored as it is in the main BEGIN/END block.
13END
14
15-- GO 
16REVOKE EXEC ON dbo.FooGetTableB TO ApplicationRole
17GRANT 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
See Also

Other Resources