September 15, 2014 at 2:12 pm
ScottPletcher (9/15/2014)
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.
Quick thought on this, looks like a lot of work, parameter addition means changing both the caller code and the procedure code, context_info means even more work as most likely multiple caller sessions have to be manipulated. Simplest solution would be a config table, first portion of the procedure checks the table if it is active or not, then a return statement after the check simply exits returning 0 if it is inactive. Could even be a schedule type table to count for different workload for each time of the day.
😎
September 15, 2014 at 2:17 pm
Grant Fritchey (9/15/2014)
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.
There's no guarantee that "empty" will raise any form of error - unless by empty you mean "empty other than a RAISERROR statement". I unfortunately have a LOT of procedures that could get called, do nothing and may not cause the caller to fail, essentially screwing up the business outcome, but without an actual red flag anywhere. We call those "landmines" around here.
This kind of request would have me camping in the CIO office with a "you've got to be S******** - I mean, kidding me" reaction. No way I pull the trigger on this kind of request without lots of cover.
----------------------------------------------------------------------------------
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 2:38 pm
Matt Miller (#4) (9/15/2014)
Grant Fritchey (9/15/2014)
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.
There's no guarantee that "empty" will raise any form of error - unless by empty you mean "empty other than a RAISERROR statement". I unfortunately have a LOT of procedures that could get called, do nothing and may not cause the caller to fail, essentially screwing up the business outcome, but without an actual red flag anywhere. We call those "landmines" around here.
This kind of request would have me camping in the CIO office with a "you've got to be S******** - I mean, kidding me" reaction. No way I pull the trigger on this kind of request without lots of cover.
Disabling by renaming or any other means is of course only feasible if there is no onward functional dependency chain, which is implied in the initial question. Better check though, seen similar things causing headaches far too often (quick fix and then fire-fighting).
😎
September 15, 2014 at 3:01 pm
Eirikur Eiriksson (9/15/2014)
ScottPletcher (9/15/2014)
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.
Quick thought on this, looks like a lot of work, parameter addition means changing both the caller code and the procedure code, context_info means even more work as most likely multiple caller sessions have to be manipulated. Simplest solution would be a config table, first portion of the procedure checks the table if it is active or not, then a return statement after the check simply exits returning 0 if it is inactive. Could even be a schedule type table to count for different workload for each time of the day.
😎
I figured an optional parameter wouldn't require any changes to existing code. That parameter would always get the default, which you could set to allow to allow all existing code to run whatever selective code in the proc you wanted to (including just immediately exit). Only new/special code that still needed the proc to fully execute would need to pass in the new parameter.
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 4:00 pm
Assuming you obviously can't make a program change, even for adding optional parameters to stored procedure call, there are safe and relatively easy measures.
With business and management approvals, decide on what to do when you want to throttle a procedure. Throttle as in how we can control a network connection; turn it on or off.
ALTER is going to lead to recompiles and statistics changes. This can be avoided beyond changing just the first one when implementing the control changes. Just use a table (that can be reused for other like cases if needed) to update a record saying if to throttle or not.
CREATE TABLE [dbo].[ExecuteOverride]
(
ID INT IDENTITY (1,1),
ProcFuncName VARCHAR(100),
Throttled BIT DEFAULT (0)
)
GO
-- Create throttled version
CREATE PROCEDURE [OriginalProc_Throttled]
(
/*Parameter List*/
{parameter_list}
)
AS
BEGIN
/*
Your code here. Return an empty set or some validation you account for.
*/
SELECT NULL FROM YourTable
END
GO
/* Rename original to unthrottled (as it normally would run) */
EXEC sys.sp_rename @objname = 'OriginalProc', @newname = 'OriginalProc_Unthrottled'
GO
/* Create replacement for original that calls according to setting */
CREATE PROCEDURE [OriginalProc]
(
/*Parameter List*/
{parameter_list}
)
AS
BEGIN
/*
Check if to be throttled and EXEC accordingly
*/
DECLARE @Throttled BIT
SELECT @Throttled = ISNULL(Throttled, 0)
FROM ExecuteOverride
WHERE ProcFuncName = 'OriginalProc'
IF @Throttled = 0-- Not Throttled
EXEC OriginalProc_Unthrottled {parameter_list}
ELSE
EXEC OriginalProc_Throttled {parameter_list}
/* Return appropriate result set */
END
GO
/* Insert throttle/unthrottle record */
INSERT INTO ExecuteOverride
SELECT 'OriginalProc', 1 -- Throttled
GO
So when a call is made by the application, nothing appears changed, but you have a control in place to decide which route to take when executing.
I have done this with the parameter method and having an IF statement in the original proc. However, the execution plan got jacked because it will save according to how the procedure is called (what parameters). I would get less than optimal results this way. However, with a simple proc that does the EXEC call for 1 method or the other, the other 2 procedures (throttled & unthrottled) are compiled to work 1 specific way. Results are clean and quick.
September 15, 2014 at 4:10 pm
The problem with a table is that one option must apply to everyone. Parameter or other methods allow customized runs, so that your own special cases can still run the entire proc code while preventing all others, who are using the defaults, from doing so.
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 16, 2014 at 2:44 pm
The topic was how to disable and enable, and appeared to be seeking a way to do so without application layer changes. As a permanent solution, there are other ways. The table method allows for an admin to still choose to run whatever they wish, while limiting users to what he/she needs to have happen with the original without actually changing the content of the original.
Most managers want to know that no code will be changed that would be untested for when the procedure needs to run as normal.
Anyway, just my opinion. I'm new to sharing them.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply