CODEWITHSUNDEEP
sql-servert-sql
Andy Williams
Andy Williams

Reputation: 907

SQL Grant Execute On Object where do I put GO?

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!

Upvotes: 0

Views: 263

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89386

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];

Upvotes: 1

gh9
gh9

Reputation: 10703

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

Upvotes: 1

Related Questions