x

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, 2010 at 11:04 PM in Default

Murali gravatar image

Murali
906 99 115 120

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

9 answers: sort voted first

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, 2010 at 06:20 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

+1 for a good option and with pictures. We ought to use them more.
Oct 13, 2010 at 10:18 AM Fatherjack ♦♦
Pictures? You can put pictures in this thing? Who knew! ;-)
Oct 13, 2010 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, 2010 at 12:57 PM TimothyAWiseman
thank you very much.....
Oct 14, 2010 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, 2010 at 01:31 AM WilliamD
(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, 2010 at 01:42 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

+1 for the office intimidation technique. It works better if you have the reputation (think ScaryDBA!).
Oct 13, 2010 at 01:52 AM WilliamD
Threatening to break an arm usually works. No +1 though 'cause I think @WilliamD nailed it.
Oct 13, 2010 at 04:39 AM Grant Fritchey ♦♦
(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, 2010 at 11:57 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

+1 for that link
Oct 13, 2010 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, 2010 at 04:37 AM Grant Fritchey ♦♦
(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, 2010 at 11:08 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

how to enable Auditing in sqlserver2008
Oct 12, 2010 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, 2010 at 11:16 PM Cyborg
(comments are locked)
10|1200 characters needed characters left

For the default trace you can get object-Creation, object-Deletion and object-Alteration events, and from all these, you can get the ApplicationName, ClientProcessID, HostName, LoginName, LoginSid, NTDomainName, and NTUserName. In other words, they can run, but they can't hide.

I use fn_trace_gettable to query the default trace to see who is fiddling with the database. There are plenty of examples of its use around.

more ▼

answered Oct 13, 2010 at 08:15 AM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x83

asked: Oct 12, 2010 at 11:04 PM

Seen: 9242 times

Last Updated: Oct 12, 2010 at 11:04 PM