how to disable the execution of stored procedures?.

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

SQL Server Administration (2000)

how to disable the execution of stored procedures?.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-08 : 08:07:26

Mades writes "Having lot of stored procedures and need to deactivate certain stored procedures from execution. Give me some suggestion to retrive the solution."

srinivasanr
Starting Member

15 Posts

Posted - 2004-06-08 : 08:25:08

Mades,
Deactivating any SP from execution is not possible unlike Oracle..
Any solution from anybody?

Wallops !!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-06-08 : 08:27:49

Why not alter the SP code to do nothing/abort?
...just remember to park the 'true sp code' somewhere safe for re-activation.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-06-08 : 12:22:03

I would just comment out the entire thing and place a SELECT GETDATE() or something there instead

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-08 : 12:39:51

You could add to the beginning of each sp:

IF Object_ID('abort_<myname>') IS NOT NULL RETURN

Then create empty tables called abort_<spnames> when you want to prevent selected ones from executing.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-08 : 14:47:04

??? Why wouldn't you just deny exec privileges to all users?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Mades
Starting Member

3 Posts

Posted - 2004-06-14 : 00:41:17

derrickleggett,
Deny will stop exec of all SP.but i need to deny certain SP only.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-14 : 01:04:06

Then just deny everyone access to that one SP.

DENY EXEC ON stored_procedure TO group1, group2, group3

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Mades
Starting Member

3 Posts

Posted - 2004-06-14 : 02:01:43

Here U see the list that r executed in SQL Query Analzer.

deny exec on insertname to public

DECLARE @RC int
DECLARE @sName varchar(8)
DECLARE @sAge varchar(8)
-- Set parameter values
set @sName = 'asdas'
set @sAge = 31
EXEC @RC = [dbtest].[dbo].[insertname] @sName, @sAge

(1 row(s) affected)

--- after denying the SP.the insertname SP is still working....
pls...hlp...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-14 : 08:08:07

If you're logged in as sa or someone who is a member of the local administrators group (if you haven't removed builtin/adminstrator which you should, there's nothing you can do to fix this. You need to fix security first.

So, you need to find out what context this proc is running under. Put this in the proc; and tell us who's executing it.

SELECT USER_NAME()

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Mades
Starting Member

3 Posts

Posted - 2004-06-15 : 00:28:51

Hi derrickleggett,
It's working fine.thankx a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources