Conditionalizing CREATE and ALTER PROCEDURE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Conditionalizing CREATE and ALTER PROCEDURE

Author

Topic

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-22 : 15:44:16

When creating a new sProc, most examples DROP the sProc if it exists, then CREATE it:

IF EXISTS(SELECT name FROM sysobjects
      WHERE name = 'MySproc' AND type = 'P')
   DROP PROCEDURE MySproc
GO
CREATE PROCEDURE MySproc
AS
...
GO

In Professional SQL Server 2000 Programming, Robert Vieira says that DROPping and CREATEing kills permissions and object dependencies whereas ALTERing doesn't.

I tried to perform CREATE PROCEDURE and ALTER PROCEDURE in an IF statement, but I am getting a syntax error near the keyword 'PROCEDURE'. This must be because CREATE and ALTER must be first statements in a batch. My code is:

IF not EXISTS(SELECT name FROM sysobjects
 	WHERE name = 'MySproc' AND type = 'P')
	CREATE PROCEDURE MySproc
	AS
GO

How can I conditionalize creation vs altering of procedures (and UDFs)?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-11-22 : 15:52:03

You can't, at least not without duplicating the code for both create and alter operations.

Best thing to do is include the permissions in your script file, and use the standard DROP/CREATE logic.

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-22 : 17:13:34

quote:
Originally posted by robvolk

You can't, at least not without duplicating the code for both create and alter operations.

What are the default permissions on sProcs and UDFs? I assume everyone can run them but only the owner or sa can alter them?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-22 : 18:57:47

The only default permissions are that the object owner, database owner, and members of the sysadmin role can execute them.

All other permissions must be granted explicitly.

CODO ERGO SUM

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-22 : 19:06:26

quote:
Originally posted by Michael Valentine Jones

The only default permissions are that the object owner, database owner, and members of the sysadmin role can execute them.

So I would have to explicitly GRANT an execute permission to all roles after creating a sproc or UDF.

Is there a technique to automatically grant certain permissions on an object to all roles added in the future?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-22 : 19:36:08

If that is what you want, you can grant execute to PUBLIC, a built in role that includes all users.

CODO ERGO SUM

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-23 : 10:33:15

quote:
Originally posted by Michael Valentine Jones

If that is what you want, you can grant execute to PUBLIC, a built in role that includes all users.

That will be useful. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources