How to modify 'system stored procedure' SQL 2008?

  • 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

  • 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?

  • 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]

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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]

  • 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

  • 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:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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:

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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 DDL Triggers

    Understanding Event Notifications

  • 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)'),

    @data

    );

    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