how to find which user dropped a table in a database

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

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

Murali gravatar image

906 87 114 120

(comments are locked)
10|1200 characters needed characters left

9 answers: sort oldest

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

![Report Menu][1]

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

[1]: /upfiles/RptMnu.JPG
more ▼

answered Oct 13 '10 at 06:20 AM

Blackhawk-17 gravatar image

11.8k 28 30 35

+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)
10|1200 characters needed characters left
Its impossible to get these details unless you have a DDL audit trigger or you have enabled Auditing or Default Trace enabled.
more ▼

answered Oct 12 '10 at 11:08 PM

Cyborg gravatar image

10.6k 36 39 45

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)
10|1200 characters needed characters left

+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.
more ▼

answered Oct 12 '10 at 11:13 PM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(comments are locked)
10|1200 characters needed characters left

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

[1]: http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
more ▼

answered Oct 12 '10 at 11:57 PM

WilliamD gravatar image

25.8k 17 19 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)
10|1200 characters needed characters left

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.
more ▼

answered Oct 13 '10 at 01:42 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

+1 for the office intimidation technique. It works better if you have the reputation (think ScaryDBA!).
Oct 13 '10 at 01:52 AM WilliamD
Threatening to break an arm usually works. No +1 though 'cause I think @WilliamD nailed it.
Oct 13 '10 at 04:39 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 12 '10 at 11:04 PM

Seen: 8541 times

Last Updated: Oct 12 '10 at 11:04 PM