Do not forget about GO

Home

Source: own resources, Authors: Agnieszka and Michał Komorowscy

Almost 4 years ago, I wrote a short post in Polish about problems that may occur if we forget about GO keyword in our scripts. I decided to write this post again, this time in English, because recently I helped to fixed exactly the same problem again. As a remainder, GO keyword instructs tools like SQL Management Studio, sqlcmd... to send the batch of T-SQL code to the server. Now, let's look at the following code that creates a stored procedure and tell me what is wrong here:

CREATE PROCEDURE dbo.pr_Fun
AS
BEGIN
    /*...*/
    RETURN
END

GRANT EXECUTE ON dbo.pr_Fun TO public
GO

If you don't see any mistake don't worry. It is very easy to omit. This script will be executed without any errors. However, if we try to execute pr_Fun stored procedure, we will see the following message:

The EXECUTE permission was denied on the object 'pr_Fun', database 'Test', schema 'dbo'.

It may be surprising because we used GRANT EXECUTE... at the end of the script. The puzzle will be solved if we examine the code of pr_Fun by using sp_helptext pr_Fun The result could be even more surprising. Try and you will see that the line with GRANT EXECUTE is actually the part of the stored procedure. And if so, it wasn't executed what led to a problem. For me it's a little bit crazy ;). Especially because if we try do the same thing for a function the parser will return the error and the script will failed.

The correct script should have GO just after END:

...
END
GO

GRANT EXECUTE ON dbo.pr_Fun TO public
GO

Now if we execute this script 2 batches instead of 1 will be send to the server and executed. Firstly the stored procedure will be created and then permissions will be granted.