September 14, 2014 at 11:53 pm
Hi All ,
How to disable and enable the store procedure?
cheers
September 15, 2014 at 12:06 am
I believe you cannot disable a stored procedure directly... but there are few ways to accomplish it...
check these links...
http://stackoverflow.com/questions/23114386/enable-disable-stored-procedures
https://go4answers.webhost4life.com/Example/disable-stored-procedure-149295.aspx
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
September 15, 2014 at 12:10 am
karthik babu (9/15/2014)
I believe you cannot disable a stored procedure directly... but there are few ways to accomplish it...check these links...
http://stackoverflow.com/questions/23114386/enable-disable-stored-procedures
https://go4answers.webhost4life.com/Example/disable-stored-procedure-149295.aspx
so can I uncheck the GRANt permission for EXECUTE ( Via right click and properties ) in that Store proc ?
September 15, 2014 at 12:20 am
Better use the sample code below..
REVOKE EXECUTE ON OBJECT::dbo.StoredProcedure
FROM LoginName;
GO
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
September 15, 2014 at 12:25 am
karthik babu (9/15/2014)
Better use the sample code below..REVOKE EXECUTE ON OBJECT::dbo.StoredProcedure
FROM LoginName;
GO
Dont u think it is the same like : uncheck the GRANT and Check the DENY ??
September 15, 2014 at 12:27 am
I tried the same and its not reflecting in the GUI. I go by the SQL Query.
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
September 15, 2014 at 12:35 am
WhiteLotus (9/14/2014)
Hi All ,How to disable and enable the store procedure?
cheers
Quick question, by disabling it do you mean make it do nothing, make it disappear from the user's view or block execution of it?
😎
September 15, 2014 at 12:49 am
Eirikur Eiriksson (9/15/2014)
WhiteLotus (9/14/2014)
Hi All ,How to disable and enable the store procedure?
cheers
Quick question, by disabling it do you mean make it do nothing, make it disappear from the user's view or block execution of it?
😎
make it do nothing because the queue is very high so i need to disable and enable it after a period of time
September 15, 2014 at 1:30 am
WhiteLotus (9/15/2014)
Eirikur Eiriksson (9/15/2014)
WhiteLotus (9/14/2014)
Hi All ,How to disable and enable the store procedure?
cheers
Quick question, by disabling it do you mean make it do nothing, make it disappear from the user's view or block execution of it?
😎
make it do nothing because the queue is very high so i need to disable and enable it after a period of time
Then Grant/Revoke is out of the question, would most likely cause errors in the caller code. Possibly the best options are:
a. Create a table or use on if it exists to control the execution, a flag can be toggled active/inactive and at the start of the execution, the procedure reads the flag.
b. Use time driven logic, i.e. conditional in the procedure makes it exit every odd number of minutes.
c. Add the logic in the caller code and leave the procedure unchanged.
😎
September 15, 2014 at 7:24 am
I'd think the easiest way to do this would be to cheat. Rename it. Then name it back.
But, that could cause errors in the code if it tries to call the proc and it's not there. But, then again, if you revoke access to the proc, you're going to see a similar error.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 15, 2014 at 7:29 am
This might be kind of silly but just alter the procedure with all the code commented out, that will effectively disable it, then when you want to turn it back on just alter it again with the code uncommented.
That would be relatively straight forward to set up as a scheduled job and wouldn't create any application errors.
September 15, 2014 at 9:24 am
WhiteLotus (9/15/2014)
Eirikur Eiriksson (9/15/2014)
WhiteLotus (9/14/2014)
Hi All ,How to disable and enable the store procedure?
cheers
Quick question, by disabling it do you mean make it do nothing, make it disappear from the user's view or block execution of it?
😎
make it do nothing because the queue is very high so i need to disable and enable it after a period of time
The stored proc is code - it sounds like you really want to disable whatever is calling it. Why not disable the job that is calling this code?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 15, 2014 at 12:09 pm
All of those seem far too disruptive to me.
Two quick possibilities:
1) Add an optional parameter to the proc which requires a certain value or the proc just exits (or does whatever limited processing you want and then exits). When you want the proc to run normally, set the default for that param to the required value: voila, everyone can use proc fully.
When you don't want the proc to be active (except as you specifically require it to be), alter the proc so the default is a non-matching value, and thus the caller must pass in the correct value to get the full proc to run.
2) Use byte(s) in CONTEXT_INFO() to have the same effect. This is more involved to get working correctly, but can work as well.
I strongly urge you not to just REVOKE all permissions, since there's no easy way to put them all back. Or to rename the proc, which would force recompiles of already compiled code, and possibly outright errors in other cases.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 15, 2014 at 1:58 pm
Grant Fritchey (9/15/2014)
I'd think the easiest way to do this would be to cheat. Rename it. Then name it back.But, that could cause errors in the code if it tries to call the proc and it's not there. But, then again, if you revoke access to the proc, you're going to see a similar error.
At first I thought you were joking as it would certainly raise error in the caller code but then the bulb started to flicker, with ProcA(working) and ProcB(empty), swap names with a three step renaming, A to C, B to A and C to B;-)
😎
September 15, 2014 at 2:05 pm
Eirikur Eiriksson (9/15/2014)
Grant Fritchey (9/15/2014)
I'd think the easiest way to do this would be to cheat. Rename it. Then name it back.But, that could cause errors in the code if it tries to call the proc and it's not there. But, then again, if you revoke access to the proc, you're going to see a similar error.
At first I thought you were joking as it would certainly raise error in the caller code but then the bulb started to flicker, with ProcA(working) and ProcB(empty), swap names with a three step renaming, A to C, B to A and C to B;-)
😎
I'm not joking. I'm not crazy about the solution either, but I sure wouldn't want to try to remove the privs and add them back, or modify the procedure and then unmodify it in some manner. It feels like renaming is less intrusive, although, again, it's sure to raise an error in the app, which also makes me uncomfortable.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply