In the last several several weeks I have seen a number of unpredicted bugs appearing inside a legacy application, many of which are based on incongruencies between your application code (classic ASP) and also the underlying SQL Server database.

For instance, a person reported a 500 error on the page a week ago that's been working properly for 5 years. I came across the page under consideration was searching for a column inside a result set named "AllowEditDatasheets", as the real column title was "AllowDatasheetEdit".

Normally I'd chalk this up to posting untested code but, when i stated, the page has worked properly for any very very long time.

I have stumble upon this a number of occasions lately - pages that never must have labored but happen to be.

I am beginning to suspect that another worker is making subtle changes towards the database, for example renaming posts. Regrettably, you will find several programs which use a typical login which was granted SA privileges, and getting rid of individuals privileges would break lots of code (Yes, I understand this really is poor design - do not get me began), so simply changing account permissions is not a viable solution.

I am searching for a method to track schema changes. Ideally, I'd have the ability to capture the Ip from the machine which makes these kinds of changes, along with the change which was made and also the date/time if this happened.

I understand I can produce a scheduled procedure that will script the database and commit the scripts to the source control system, that will tell me when these chages occurr, but that does not help much me discover the source.

Any suggestions?

If you work with SQL Server 2008 and above, you should use SQL Server Audit.

With earlier versions, you might have the ability to add triggers somewhere tables that hold schema information and log changes to individuals.

The default trace already tracks schema changes.

In Management Studio you are able to right click on the node from the database of great interest and in the reviews menu see the report "Schema Changes History" that pulls its data after that.

When the information recorded there's not sufficient you can include a DDL trigger to do your personal logging (e.g. recording [cde] though that may be spoofed)