Sometime I have been asked that how to find out who dropped/altered/created table/stored procedure. Well, SQL 2005 has out-of-the-box solution to this problem. This works in SQL 2008 as well.

Schema Change History is the report which would give details. This report takes data from default trace which is enabled by default in SQL Server 2005 default installation. Below command will help you in finding current status.

USE master;
EXEC sp_configure ‘show advanced option’, ‘1’;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ‘default trace’;
GO

If its enabled you would see run_value as 1.

More about default trace can be read here

If you want to see who dropped a database, you can right click on Server Icon and go to Reports > Standard Reports > Schema Changed History. This Reports output would changed based on where you right clicked it (on server icon or database name)

Since default trace will be maximum 100 MB in size you may not get the data which is pretty old. Every restart also creates new file. So, if your SQL Server has restarted five times after someone dropped the object, you will not be able to see it here.

SQL Server 2005 has many other “Standard Reports” which can be found in SQL Server Management Studio.

Enjoy!!!

<Tags> Who dropped my database, when database was dropped, someone deleted database objects, tables, procedures, list of dropped objects in SQL Server 2005, Schema Change history Report, see who dropped databases, how to find who dropped the database in SQL server 2005 objects dropped</Tags>

Cheers!
Balmukund