Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002

    Unanswered: Trace changes to stored procedures

    Hi all,

    Is there a way to trace changes made to stored procedures, but not only trace wether a SP is changed or not, but also who did it, and if possible what kind of change it was.

    I was looking at the sp_trace_* procedures, but except for the checking of a possible recompilation of a SP, there are no other events available that trace changes in an SP.

    Any suggestions ? Thx in advance,


  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    Not easy. You can run this code against your database:

    sum(cast(checksum(syscomments.text) as bigint)) Object_Checksum
    from sysobjects
    inner join syscomments on =
    group by id

    ...and save the results in a table. Then you can run the code later and see if the checksum values for any object have changed.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Keep in mind that all of these comments are specific to SQL Server 2000, they probably won't apply (at least "as is") to future versions. There are a number of ways to do what you've requested, but they all have caveats of one form or another.

    C2 auditing is the holy grail. It audits everything, there are no loopholes, and you'll get more information about who did what, when, and how than you have any real use for. The caveat in this case is that it is expensive to implement, in terms of hardware, bandwidth, and administrator time spent on management.

    You can get a really lightweight monitoring system set up using the SQL Profiler trace ability that you were considering. Watching for the sequences that might be of interest in a batch isn't outrageously difficult, and it can be quite effective IF you cover all of the bases.

    You can also write a simple SQL-DMO application that will snapshot the procedures at some arbitrary frequency and check them into a source code archive such as VSS. This won't track the who, but it will give you a good estimate of the when and will permanently store the changes over time.

    There are a number of other options, but they are either outrageously expensive, more difficult to manage than C2 (at least in my opinion), or have security holes that I find unacceptable.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts