TransWikia.com

SQL Grant Execute On Object where do I put GO?

Stack Overflow Asked by Andy Williams on December 30, 2021

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!

2 Answers

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

Answered by David Browne - Microsoft on December 30, 2021

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

Answered by gh9 on December 30, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP