Problem writing one script to Drop, re-Create, and Grant Execute Permissions to a stored procedure


I'm hoping that someone may know something about this: We're writing a script (t-sql in SQL Server 2008) to all in one: 1) Drop a stored procedure, then 2)create the just dropped stored procedure, and finally 3) grant Execute permissions to the newly written procedure.

The problem is, the code we wrote when executed doesn't work. However we've pulled (from SQL Compare) code that does the exact same thing, which works (for the creation and graning permissions). We've compared the code of both scripts, the SQL Compare code works, but ours (written by hand) doesn't and they are practically identical.

Does anyone have any ideas how to help us get our hand-written code to work?


more ▼

asked Dec 10, 2010 at 11:28 AM in Default

avatar image

33 2 2 6

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The most likely reason is the lack of the go terminator between the drop and create. If this is the case then you are probably greeted with lovely

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

error. Please check that your are not missing any batch terminators. One of the most evil things to do in T-SQL is to forget terminating the procedure definition, because without it, the procedure text will include the rest of the script you are trying to execute, which can lead to some really unwanted results. Once I accidentally ran into the existing procedure definition which included something as nasty as checking existence and creation of some index, simply because the procedure definition did not end with the word GO, several scripts were compiled into a single script and the whole batch then got deployed. You can use the template like this for your create proc script:

 -- drop proc your_proc_name
 if  exists 
     select * from sys.objects 
             [object_id] = object_id(N'[dbo].[your_proc_name]') 
             and [type] in (N'P', N'PC')
     drop procedure [dbo].[your_proc_name];
 set ansi_nulls on;
 set quoted_identifier on;
 -- create proc your_proc_name
 create proc dbo.your_proc_name
     @your_param varchar(50)
     set nocount on;
         from your_tables
         where your_column = @your_param
     set nocount off;
 -- grant execute permissions to your_role
 grant execute on dbo.your_proc_name to your_role_name;


more ▼

answered Dec 10, 2010 at 11:39 AM

avatar image

20.6k 3 7 29


Thank you for the response. I am learning a lot about SQL Server as I go, so I hope that you don't mind if I have a couple questions about your post?

Where the errors for the hand-written script seem to be appearing is on the lines with the GO statement.

I thought the set "set ansi_nulls on;" and the "set quoted_identifier on;" statements were automatically just included (by SQL Server) when you hand-write a stored procedure (when not right-clicking on the Stored Procedures folder in the GUI and selecting 'New Stored Procedure'.

Will adding the "set nocount off;", "set ansi_nulls on;", "begin;", etc. make a diffeence in how the script performs. The SQL Compare tool that wrote a comparable script (which resembled ours) didn't have any of those lines of code in it, but seemed to work pretty well (although it didn't do the drop, it performed the creation of and permissions granting flawlessly).

Thank you very much for your insight and help.

Dec 10, 2010 at 12:21 PM Head_Contusion

The settings set ansi_nulls on; and set quoted_identifier on; should be added if they are not present. These are called sticky settings, they are very important, and cannot be changed inside of the body of the stored procedure. In other words, if you forget to add, say, set ansi_nulls on; but your procedure logic really needs it then in case if the session settings have it off for some obscure reason, changing it inside of the proc definition will not work. Since you never want to rely on the session settings of the future code which will call your proc, you will potenially save yourself a lot of trouble by simply adding the sticky settings before the proc definition. set nocount on; is usually added after begin and set nocount off; (optionally) before end. The setting is responsible for supressing the X rows affected messages which show up in the messages pane. The setting is therefore desired. GO is not technically a T-SQL command, but is rather a batch separator. It must be present by itself on the separate line and cannot have ; after it. Do not forget to add GO after end. Please reply if you have any more questions. Thanks.

Dec 13, 2010 at 02:20 PM Oleg

Great information, thank you very much, Oleg.

Dec 22, 2010 at 11:40 AM Head_Contusion
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 10, 2010 at 11:28 AM

Seen: 3528 times

Last Updated: Dec 13, 2010 at 02:22 PM

Copyright 2018 Redgate Software. Privacy Policy