how to find which user dropped a table in a database

how to find which user dropped a table in a database.

asked Oct 12 '10 at 11:04 PM in Default

Murali
896 ● 64 ● 100 ● 117

(comments are locked)

9 answers:

In SSMS you can try right-clicking on the dB and navigating through Reports -> Standard Reports -> Schema Changes History.

Report Menu

Note that this is based on the default trace so your mileage may vary.

answered Oct 13 '10 at 06:20 AM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

+1 for a good option and with pictures. We ought to use them more.

Oct 13 '10 at 10:18 AM Fatherjack ♦♦

Pictures? You can put pictures in this thing? Who knew! ;-)

Oct 13 '10 at 12:35 PM ThomasRushton ♦

@Fatherjack, definitely. Pictures are good.

@Blackhawk, I know you said it in your answer, but it's worth really highlighting that this depends on the default trace...

Oct 13 '10 at 12:57 PM TimothyAWiseman

thank you very much.....

Oct 14 '10 at 12:57 AM Murali

There is an impact, because actions are being "watched" and audited in the trace file. The impact is minimal though and the default trace will be running unless you explicitly turned it off. You can safely leave this turned on, unless you are experiencing problems (bottlenecks on the drive where the trace is located)

Oct 14 '10 at 01:31 AM WilliamD

(comments are locked)

Its impossible to get these details unless you have a DDL audit trigger or you have enabled Auditing or Default Trace enabled.

answered Oct 12 '10 at 11:08 PM

Cyborg
10.2k ● 29 ● 39 ● 44

how to enable Auditing in sqlserver2008

Oct 12 '10 at 11:09 PM Murali

Check out this technical article from microsoft http://msdn.microsoft.com/en-us/library/dd392015(SQL.100).aspx

Oct 12 '10 at 11:16 PM Cyborg

(comments are locked)

+1 to cyborg. You can also find out IF you have a trace (profiler) and maybe it can be found in the default trace started by SQL server.

Even if you know who did it, you may need to restore the table. The bad news is that you may need to use one of your backups and may loose any other changes. The good news is that you can use Red-gate object level restore or virtual restore to restore the table you lost.

If you don't have a recent backup, then i feel sorry for you.

answered Oct 12 '10 at 11:13 PM

Håkan Winther
15k ● 29 ● 35 ● 46

(comments are locked)

Ading to Hakan's answer, you can use the default trace to follow DDL changes. Take a look at this article from SSC

answered Oct 12 '10 at 11:57 PM

WilliamD
25.3k ● 16 ● 18 ● 41

+1 for that link

Oct 13 '10 at 12:02 AM Cyborg

With 2005 & 2008, the default trace is there. Depending on how long after the fact and how much of the activity monitored by the default trace has occurred, you should absolutely be able to get that information from the default trace.

Although, I like @Fatherjack's idea too.

Oct 13 '10 at 04:37 AM Grant Fritchey ♦♦

(comments are locked)

If none of the other answers help you then stand up in your office and shout "Who deleted table -inserttablenamehere- ?" and then look for the most embarrassed person avoiding your attention ...

Seriously though, unless you have something that the other answers have suggested in place before the drop took place then you wont find it. The best you can do is see who has permission to drop tables from the security settings on the server/database and then send a polite email to all of those people about how the table is missing and that if anyone needs to drop an object can they make sure noone else needs it before they run the script.

answered Oct 13 '10 at 01:42 AM