Who created/modified/deleted an object on a database?

Do you ever wanted to know who created/modified/deleted a table, a stored procedure, an index, a view, a trigger or on a database?  SQL Server provides a Standard Report for this purpose and it uses the default trace as the source of information. The name of the report is Schema Changes History.

To run the Schema Changes History report open SQL Server Management Studio, make a right click on an object (the instance name or a database name, for example), then select "Reports", click on "Standard Reports", and then click on "Schema Changes History" report.

The result is the following report.






As you can see the report generated provides the database name, the boject name, the object type, what type of DDL operation took place, and the login name that made the changes.

The SQL Server default trace is used to generate the Schema Changes History report. The default SQL Server trace keeps 5 default trace files on the same folder where the SQL Server Error Log files are located, and once the fifth files is full it starts to use the first one. Each trace file has a limit of 20 MB. So the report helps to track what happened the last few days.




 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.