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.