Hello all,

Have you wondered if there was a way to find out if someone made a modification to a table, created a new stored procedure or dropped a table in a Sage 500 database?

This time we will look at a way to find out who made changes to objects in your Sage 500 database. What type of changes? Creates, Alter, or Drops. Note that this is not a tool within the Sage 500 application, but a  feature(report) that is part of SQL Server Management Studio(SSMS). Its called the Schema Changes History report.  Its one tool that can be used to find out who deleted or changed an object.

What does the report look like? Here is a sample:

There is not a lot of detail, but it does show the essentials.  This report shows who created, dropped, altered objects in a database. If something was altered, the report does not show what specifically was altered. Only that it was. The information the Schema Changes History report provides, is driven from the data collected by the SQL Server default trace. This schema report does not capture any DML(Data Manipulation Language) statements (Deletes, Updates, or Inserts). So if you are looking to find who deleted lots of data from a particular table, this report would not help you. 

Ok, how do I launch the report?

First In order for this report to be able to show any information, the SSMS default trace must be enabled.  During installation of SSMS it normally is enabled, so likely, you will not need to do anything. One way to find out is to create a new query window in SSMS. Then, execute  sp_configure 'default trace enabled' . If the run_value =1, then the default trace is enabled.

From SSMS, after having logged into it, the report can be launched from here:

In my example I created a table named TEST123 and added a column to that table.  Here is what that sequence looks like and following is the Schema Changes History report :

From the photo above, you can see that the Schema Changes History report, captured information that I created a table and then I altered that table.  Also notice, that the report does not show, how I altered the test table.  It did not log that I added another column named field2.  Because the default trace eventually will roll over into a new default trace file, the information you see on the report today may not show in future reports. If you want to keep the report results for historical purposes, you want to save it as a screenshot into a history folder. 

If you need to track down a change, drop, or creation of a particular object, and want a quick and easy way to check, give this Schema Changes History report a try!

-Tony