SQL Grant Execute On Object where do I put GO?

Question

Confused on the location of a GO when granting execute permission on a stored procedure to a role
Approach #1:
GO
GRANT EXECUTE
    ON OBJECT::[dbo].[StoredProcedure] TO [AppRole]
    AS [dbo];
GO

Approach #2:
GRANT EXECUTE
    ON OBJECT::[dbo].[StoredProcedure] TO [AppRole]
    AS [dbo];
GO

Approach #3:
GO
GRANT EXECUTE
    ON OBJECT::[dbo].[StoredProcedure] TO [AppRole]
    AS [dbo];

Which is correct? Thanks!

David Browne - Microsoft · Answer

If you are appending this to your CREATE OR ALTER procedure batch, you definitely need GO between the stored procedure body and the GRANT.
CREATE OR ALTER PROCEDURE StoredProcedure
AS
BEGIN
  ...
END

GO  --this is required to prevent the `GRANT` from being part of the procedure

GRANT EXECUTE
    ON OBJECT::[dbo].[StoredProcedure] TO [AppRole]
    AS [dbo];

gh9 · Answer

In Your scenario Go should be the last command. All Go does is signal the code to be processed. So there are scenarios where you might want to put go in between sql commands. For example
CREATE TABLE ....
GO
UPDATE TABLE ....
GO

MSDN GO Statement

SQL Grant Execute On Object where do I put GO?

2 Answers

Add your own answers!

Ask a Question