Tracing changes in SQL Server Stored Procedure

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 ?
LVL 2
dirkvdoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

winzigCommented:
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 :)
0
dirkvdoAuthor Commented:
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!).
Thanks.
0
SjoerdVerweijCommented:
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.
0
dirkvdoAuthor Commented:
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.
0
SjoerdVerweijCommented:
dirkvdo: Did you just get hired at Thunder Valley or something?

Anyway:
- 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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.