SA0150 : The procedure grants permissions at the end of its body. Possible missing GO batch separator command
The topic describes the SA0150 analysis rule.
The procedure grants permissions at the end of its body. Possible missing GO batch separator command
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.
The rule has a Batch scope and is applied only on the SQL script.
The target of on which the permissions are granted or revoked.
The rule does not need Analysis Context or SQL Connection.
Design Rules, Security Rules
There is no additional info for this rule.
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
|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|