Enable/Disable stored procedures

Question

I am working with a large group of stored procedures (~300) in SQL Server 2012 Web Edition and I need to enable/disable some of the stored procedures at various times.

Is there a way to create a sub-folder under "Programmability" in SQL Server Management Studio to place inactive stored procedures in? If not, is there an option to enable/disable a specific stored procedure without removing it from the database? Since I frequently need to enable/disable I would rather not remove the procedure and then re-script it when it is needed?

Is it just that you want them out of the way, or do you want them to be impossible to execute? — David Atkinson, Apr 16 '14 at 16:24
More just "out of the way" yet easily restorable. Like if I could create a folder that said "disabled". — webworm, Apr 16 '14 at 19:22

score 4 · Answer 1 · answered Apr 16 '14 at 15:58

4

Create those stored procedure in a separate schema (use CREATE SCHEMA) and then GRANT or REVOKE execution right on all stored procedures in that schema for the users in charge.

answered Apr 16 '14 at 15:58

bjnr

3,353
1
18
32

score 1 · Answer 2 · answered Apr 16 '14 at 16:01

As a suggestion, you can have contract in naming for example spTest is enables and spTest_Disabled is the same one which is disabled, so you can write another storedprocedue with this sugnature spChangeSpStatus(spName,state) this sp uses sp_rename to rename the procedure to new one,(I mean adding _disabled to its name or remove _disabled form the name)

I hope this helps

score 1 · Answer 3 · answered Apr 17 '14 at 06:50

You can't create a subfolder, but can create another schema. Name it whatever you like (say inactive) and set permissions on it appropriately.

Then write a script to move objects from the working schema (say dbo) to inactive:

alter schema inactive transfer dbo.proc

score 1 · Answer 4 · answered Apr 17 '14 at 10:23

There is an inexpensive tool called SQLTreeo that allows you to create folders in the Object Explorer. If I remember correctly, there are two modes of operation, one that allows each user with the tool to have their individual folder structure, and another that imposes a common folder structure appear on all machines that have SQLTreeo installed.

score 0 · Answer 5 · answered Aug 06 '15 at 17:58

Create a new table to manage your SP, and in each SP just add an IF that always ask for the table created dbo.ManagerSP if that sp is Active=true, if true the SP runs,,,if not,,,not runs,,

And with that for control all your SP just need run an UPDATE of active status in dbo.ManagerSP

Enable/Disable stored procedures

5 Answers5