Post reply

Can we trace who changed the code for the SP ?

  • July 29, 2008 at 3:51 am

    #70626

    There was 1 common SQL Server login being used to execute the script. By mistake or intention the code in another SP is found to be modified. How can we track who and when made the changes ??

    Pls suggest

    Thanks !

  • July 29, 2008 at 3:58 am

    #849646

    Have you tried SQL profiler, I am sure it should tell you who logged in and changed it, there should be an option in there that tells you who was logged in at the time it was changed.

    Terry

  • July 29, 2008 at 4:07 am

    #849652

    In Profiler trace the Object altered/created/deleted events.

    "Keep Trying"

  • July 29, 2008 at 4:11 am

    #849654

    The code has been changed, we got to know.. later. Is there any way out , now...??

    Profiler tracing would have helped in case it had been enabled to sav ethe traces.

    Also , as it is a production server , Its is not advised to enable profiler tracing.. right ??

  • July 29, 2008 at 4:18 am

    #849661

    If you didn't have some form of tracing enabled, there's no way to tell who changed the proc.

    As for profiling a prod server, via the GUI is a fairly bad idea, but tracing infrequent events via the server-side trace procedures (sp_trace*) is fine.

    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

  • July 29, 2008 at 4:24 am

    #849665

    Hi Gail ,

    Thanks for guidance !

    Can u pls tell about how and when to run the sp_trace * , and which are those infrequent events , which we should trace n take care..??

  • July 29, 2008 at 7:02 am

    #849743

    Look up the sp_trace stored procedures in books online. That should give you a good overview of how they work. You can create a trace in profiler, then export the trace definitions to a script file. It's easier than writing it from scratch.

    Unless you decide to trace things like lock acquired and or object accessed, you shouldn't have a problem.

    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

  • July 29, 2008 at 3:36 pm

    #850172

    You have got to be carefull that no one share an account. Otherwise, your trial will be useless for someone who does it on purpose.

    When you try to trace the activities, you had better send you traced data to some other place to avoid the hicker wiping out logs.

    Normally, you can find out when your stored procedure is modified, you may try to check your eventviewer to find who was on this server in that period.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply