Share via

How to find SQL server user log history?

  • Article

Question

Monday, October 15, 2012 8:18 AM

I am using Microsoft SQL Server 2008 R2. In my server there are some Database. Now some body has deleted 2 database of them. Now i want to find him/her. 
 
Is there any system to trace it?
 
Thanks in advance
 
Rashed

All replies (6)

Monday, October 15, 2012 8:31 AM ✅Answered

Hello Rashed,

In SSMS "Object Explorer" do a right-mouse click on the server node => "Reports" => "Standard Reports" => "Schema Changes History", there you can see all schema changes included database creation / drop.

Olaf Helper

Blog Xing

Monday, October 15, 2012 10:43 AM ✅Answered | 1 vote

Rashed,

The report bases on the SQL Server standard traces; this are the 5 trc files in LOG folder of your SQL Server.

The standard trace file can have a max size of 20 mb and and they are filled in robin-robin procedure; older trace information will be removed when new info are saved; so that's the limitation of the standard trace.

Olaf Helper

Blog Xing

Monday, October 15, 2012 9:15 AM

Olaf,

This is applicable for existing Database. But i need some things to trace far a deleted database. 

Suppose DB Name: TestDB and it is already deleted by some body. Now i want to find who delete it an when.

Thanks

Rashed 

Monday, October 15, 2012 9:39 AM

Rashed,

The report contains also "DROP database" events; you can export the report to MS Excel and search there for DROP events.

Olaf Helper

Blog Xing

Monday, October 15, 2012 10:26 AM

Olaf,

Thanks for ur help. it is working..

But i have a query. Can i change the time period 

**Schema Change History (Since 10/12/2012 4:11:50 PM ) **

Form where this reports are taking this time period.

Rashed

Monday, October 15, 2012 9:36 PM

In addition to Olaf's posting: if you want more reliable auditing of events, you can employ DDL triggers or event notification. The default trace is just a quickie, and if someone wants cover his tracks, this can easily be done by generating of actions that are covered by the default trace, so that that the files roll over.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se