Help : Who dropped objects from database?
Posted by blakhani on June 8, 2011
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
Help: SQL Server 
Priyanka said
Nice..article…can’t it possible to get deleted db name after restarted sql server more than five times.
i want deleted db name my db may be deleted before a month??is it possible if yes then pls help me for this
blakhani said
Hi Priyanka,
Thanks for reading the post. (Sorry for delay as I was on vacation) As you already noted that default traces would not be an option. Here are the other ways I can think of.
1> If you want name then you can look for “Starting up database” in ERRORLOGs and see if you have old file (mostly this would not work unless you have increased default number of errorlog files from 6 to higher number, because they are also like trace files
2> “Starting up database” in also written in Event log and there are better chances to find it there.
Hope this helps.
Balmukund
Priyanka said
Thankx for the reply..but didn’t find that
Krishna said
Yes, Its really good article. Here i am facing one issue.
It was already enabled the default trace flag and i have altered some command. These records are showing in Trace flag but it doesn’t show in Schema Changed History.
I have tried to disabled and enabled the trace flag but still it is showing empty report
Please can you help me.
blakhani said
Krishna,
There are two “Schema Change History” reports. One at database level and another at server level. Which one you selected?
Krishna said
Thanks for quick reply…. We have tried with database and server level. both empty..
blakhani said
I need to have a look.. Can you ping me at blakhani (at) koolmail (dot) in?
Mukesh said
This is exactly what I wanted.
Cheers
Siva said
I checked default trace is enabled and it is not listing any trace when I select from Server and Database level. run_value is 1.Thanks in advance.
blakhani said
Traces run in rollover mode and recycles on SQL restart. If it’s old data or SQL is restarted multiple times then no data would be shown.
Pradip nikalje said
It works..Thanx…!!!
blakhani said
Thanks!
Bhoj said
It works fine. Thanks !!
Help : Who dropped login from server? « Help: SQL Server said
[…] time ago I blogged about Who dropped objects from database and it was very useful for many readers. Few days back someone asked me via Facebook – “Is it […]
sunppatel said
It is inbuilt report for viewing a updated objects. It is good. Thanks !!!
blakhani said
Thanks!