[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

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

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

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.