We had a Stored Procedure written and it had a GRANT written at the last line of the SP.. Our DBA recommended that there should be a GO before the GRANT statement, else it will be executed every time.
What does this mean? I am not sure how GO will prevent GRANT executing "every time".
Your DBA meant that this GRANT will be a part of your stored procedure, and as such will execute every time your procedure is executed. Typically users should not be allowed to grant privileges, so your procedure should raise errors every time its last command is executed.
A stored procedure definition doesn't have to end with and END. If you define your procedure
like this:
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 execute permissions on that stored procedure to user1.
If you do something like this, though:
CREATE PROCEDURE MySP AS
SELECT field1, field2 FROM table;
GRANT EXECUTE ON MySP TO user1;
...then SQL Server will create a procedure that both returns a result set, and grants execute permission every time that stored procedure is executed. In other words, the GRANT is included in the definition of the stored procedure. Probably not what you wanted.
A GO statement is used to mark the end of a batch in SQL Server. It unambiguously tells SQL Server "I'm done with whatever I was executing in the previous set of statements". It's very good practice to add this to the end of every definition in your create scripts; this exact situation has bitten me more than once in the past.
GO is the default command separator used in query analyzer, you can actually configure it to be whatever you want. you could also achieve the same by using BEGIN..END around the proc body.