Tracing changes in SQL Server Stored Procedure

Posted on 2004-11-25
Medium Priority
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
  • 2
  • 2
LVL 10

Expert Comment

ID: 12677050
All changes are stored in  Transaction Log, you cane use tools like http://www.lumigent.com/products/le_sql.html
you will be able track data, schema and permision changes :)

Author Comment

ID: 12679285
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

ID: 12701522
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.

Author Comment

ID: 12703467
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

SjoerdVerweij earned 150 total points
ID: 12708072
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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…
Suggested Courses

807 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