Tracing changes in SQL Server Stored Procedure

Posted on 2004-11-25
Last Modified: 2008-01-09
Hi all,
I'm looking for an easy way to track changes that are applied to Stored Procedures. At present developers at our site can modify stored procedures (as well as other objects) and we want to track when a modification is applied, and if possible also by whom. If it could be done to also trace what kind of changes were applied, that would be even better.

I've been looking into sp_trace_setevent procedures to trace object changes, but that's getting me nowhere.

Any idea's anyone ?
Question by:dirkvdo
    LVL 10

    Expert Comment

    All changes are stored in  Transaction Log, you cane use tools like
    you will be able track data, schema and permision changes :)
    LVL 2

    Author Comment

    Hi Winzig,

    Are you actually using the LogExplorer ? I can't find any references to tracking changes in Stored Procecures in the online folders and documentation of LogExplorer. But if it can show me the changes, then my worries are over (for a couple of minutes at least!).
    LVL 18

    Expert Comment

    Never allow direct modification of stored procedures. Use scripts stored in SourceSafe (or another source control system), and always create your procedures with encryption on.
    LVL 2

    Author Comment

    Thanks Sjoerd,

    But that's the main problem: there's no source control system installed, and historically every developer of the team can logon to SQLServer as 'sa', because they need to run system traces. I am in the process of proposing an important policy change, but these things take time, and in the meanwhile we can't track the changes made.
    By the way, why using encryption ?

    But nevertheless thanks for the valuable comment.
    LVL 18

    Accepted Solution

    dirkvdo: Did you just get hired at Thunder Valley or something?

    - They don't need to be 'sa' to trace -- just create seperate accounts (or, preferably, make the Windows accounts admins).
    - You can start by using CVS or something to get some source control going.
    - If you do not encrypt the procedures on the server, sooner or later some developer WILL start making changes on the server -- making your scripts outdated, and Bad Things(TM) happen.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Suggested Solutions

    I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now