February 18, 2010 at 1:49 am
Hello all,
Have anybody were successful in attempt to modify SQL Server 2008 'system stored procedure'?
Lets say that for some really insane reason you want to modify 'system stored procedure' like "sys.sp_add..." in your fine MS SQL Server 2008 server... how one can go about it?
cheers,
~Leon
February 18, 2010 at 2:03 am
As per my understanding and exp one should never modify any system objects...if you really wish some modification why dont you copy the code of system SP to new SP and do modification you want in newly created SP?
February 18, 2010 at 9:27 am
Leon Orlov-255445 (2/18/2010)
Hello all,Have anybody were successful in attempt to modify SQL Server 2008 'system stored procedure'?
Lets say that for some really insane reason you want to modify 'system stored procedure' like "sys.sp_add..." in your fine MS SQL Server 2008 server... how one can go about it?
While I believe that it is possible, it should NOT be done because:
1. It's pretty hard to do.
2. It's dangerous to your Server.
3. It's not supported by Microsoft.
4. It's will void your warranty.
5. It's probably unnecessary.
Given all of the above, I will not be telling someone in the "Newbies" forum how to do something that they should not be doing and that could harm them or someone else.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 18, 2010 at 11:52 am
Yes, you shouldn't mod sys procs... but remember "insane reason" to do so.
So, with SQL 2008, is it still possible and if so, how one be able to do so?
BTW. never mind voiding warranty, MS support, or any of the best practice guidelines for db server administrations and what not.
February 18, 2010 at 12:10 pm
As far as I'm aware, you can't modify the system procs and there is no reason, insane or otherwise, to try to do so.
Write your own procs, put them in master and mark them as system objects if you like, but stay away from the system objects.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2010 at 2:40 pm
Leon Orlov-255445 (2/18/2010)
Yes, you shouldn't mod sys procs... but remember "insane reason" to do so.
Um, no. You should remember "Newbie", "dangerous" and "unnecessary". I would no more tell you how to do this than I would tell an 8-year-old how to load a gun over the Internet.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 18, 2010 at 3:04 pm
While there may be a way to do it, there is no good reason to do so, as has been stated several times. As well as the fact that as soon as you apply a service pack or even a patch you could easily lose your change.
I am 100% sure that there is a better way to do whatever you want to accomplish that doesn't require mods to system objects..
CEWII
February 18, 2010 at 4:45 pm
GilaMonster (2/18/2010)
As far as I'm aware, you can't modify the system procs and there is no reason, insane or otherwise, to try to do so.Write your own procs, put them in master and mark them as system objects if you like, but stay away from the system objects.
I vote for this ...this is the correct one for you, Leon! :w00t:
February 18, 2010 at 5:05 pm
Gail, Barry, and Elliott have put it very succinctly.
If you really want slightly different performance from one of the system procs, create on of your own that fills that requirement or suggest a mod or new one be created to MS. If they find it to be useful in widescale, they may make a change or create a new one for future release. Emphasis is on may. It never hurts to ask - but take it to the owner of the product.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 18, 2010 at 5:46 pm
I'll throw in my 2 cents, as well. Do not ever modify a system stored procedure. If you need some of the functionality with an "insane" change, then script out the proc, make the desired changes, and save it as a new proc.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2010 at 8:47 pm
Ok. Ok. I know I don't want to modify system procs. We don't need to get into the details and reasons why.
Stupid question is when you right click on the stored proc - it does give you an option to "Modify" and even 'spill out' ALTER PROCEDURE.. script but that is a design question.
Here is the example of what one might want to do.
Sys proc sp_addrolemember... called directly from the add new user event when you click check box next to any system roles beside 'public. Well, the question is... if we want to trigger an event, like we are required to take some further actions right on the spot, if you will. Actions like raise an error or perhaps print/display an error message or even simply fire another user stored procedure... with it own series of events. What are the option?
:w00t:
February 18, 2010 at 9:20 pm
First though was:
sp_helptext
What it sounds like is you want to change the behavior of the GUI. I'm sorry, that is an even poorer plan than just changing system objects. Write a sproc that you call that calls whatever system sprocs you want.
CEWII
February 19, 2010 at 1:03 am
Leon Orlov-255445 (2/18/2010)
Sys proc sp_addrolemember... called directly from the add new user event when you click check box next to any system roles beside 'public. Well, the question is... if we want to trigger an event, like we are required to take some further actions right on the spot, if you will. Actions like raise an error or perhaps print/display an error message or even simply fire another user stored procedure... with it own series of events. What are the option?
DDL trigger?
Event notifications?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2010 at 6:49 am
Almost certainly a DDL Trigger. Event Notifications are asynchronous. If you want to be able to roll back the action, you have to be part of the transaction.
Understanding Event Notifications
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 19, 2010 at 7:22 am
If you really need to intervene ... use a ddl trigger
If you only need a notification, you might consider the asynchrone path.
example ddl trigger :
create TRIGGER [ddlDatabaseTriggerNonSA]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
-- Restrict DDL to Func and Sproc for non-sa
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
-- just for test
IF @object IS NOT NULL
PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;
ELSE
PRINT ' ' + @eventType + ' - ' + @schema;
IF @eventType IS NULL
PRINT CONVERT(nvarchar(max), @data);
IF NOT ( @eventType LIKE '%function%'
OR @eventType LIKE '%procedure%' )
BEGIN
IF IS_SRVROLEMEMBER ('sysadmin') = 0
BEGIN
Off course you need to get rid of this line but now you know you have to TEST it
RAISERROR ('You are not entitled to perform this modification [%s]' , 1,1,@eventType) WITH log
ROLLBACK TRAN
END
END
INSERT [dbo].[T_AuditDbDDLLog]
(
[PostTime],
[DatabaseUser],
[LoginUser],
[OriginalLoginUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
(
GETDATE(),
CONVERT(sysname, CURRENT_USER),
CONVERT(sysname, SUSER_SNAME()),
CONVERT(sysname, ORIGINAL_LOGIN()),
@eventType,
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
);
END;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply