Why should GRANT be written after GO in an SQL stored procedure?

Question

We had a recorded procedure, and she had GRANT written on the last line of the SP. Our database administrator recommended that GO be before the GRANT statement, otherwise it will run every time.

What does it mean? I'm not sure how GO will prevent GRANT from executing “every time”.

Answer 1

The definition of the stored procedure should not end with and END. If you define your procedure for example:

CREATE PROCEDURE MySP AS
SELECT field1, field2 FROM table;

GO

GRANT EXECUTE ON MySP TO user1;

... then SQL Server will create a procedure that returns a result set, and grant permissions to execute this stored procedure on user1.

If you are doing something like this:

CREATE PROCEDURE MySP AS
SELECT field1, field2 FROM table;

GRANT EXECUTE ON MySP TO user1;

... then SQL Server will create a procedure in which it returns the result set and grants permission to execute each time this stored procedure is executed. In other words, GRANTincluded in the definition of a stored procedure. Probably not what you wanted.

Operator

A GO SQL Server. SQL Server " , ". ; .

Answer 2

, GRANT , , , . , , .

Answer 3

GO - , , , . , BEGIN..END proc.