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 newest

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

In addition to all the answers to this question I want to share a script from a collegue of mine. This script is using the default trace to list who changed what. The complete blog post can be found at [http://www.sqlservice.se/?s=default+trace][1]

SELECT  TE.name ,
v.subclass_name ,
DB_NAME(DatabaseId) AS DBName ,
T.NTDomainName ,
t.NTUserName ,
t.HostName ,
t.ApplicationName ,
t.LoginName ,
t.Duration ,
t.StartTime ,
t.ObjectName ,
CASE t.ObjectType
WHEN 8259 THEN ‘Check Constraint’
WHEN 8260 THEN ‘Default (constraint or standalone)’
WHEN 8262 THEN ‘Foreign-key Constraint’
WHEN 8272 THEN ‘Stored Procedure’
WHEN 8274 THEN ‘Rule’
WHEN 8275 THEN ‘System Table’
WHEN 8276 THEN ‘Trigger on Server’
WHEN 8277 THEN ‘(User-defined) Table’
WHEN 8278 THEN ‘View’
WHEN 8280 THEN ‘Extended Stored Procedure’
WHEN 16724 THEN ‘CLR Trigger’
WHEN 16964 THEN ‘Database’
WHEN 16975 THEN ‘Object’
WHEN 17222 THEN ‘FullText Catalog’
WHEN 17232 THEN ‘CLR Stored Procedure’
WHEN 17235 THEN ‘Schema’
WHEN 17475 THEN ‘Credential’
WHEN 17491 THEN ‘DDL Event’
WHEN 17741 THEN ‘Management Event’
WHEN 17747 THEN ‘Security Event’
WHEN 17749 THEN ‘User Event’
WHEN 17985 THEN ‘CLR Aggregate Function’
WHEN 17993 THEN ‘Inline Table-valued SQL Function’
WHEN 18000 THEN ‘Partition Function’
WHEN 18002 THEN ‘Replication Filter Procedure’
WHEN 18004 THEN ‘Table-valued SQL Function’
WHEN 18259 THEN ‘Server Role’
WHEN 18263 THEN ‘Microsoft Windows Group’
WHEN 19265 THEN ‘Asymmetric Key’
WHEN 19277 THEN ‘Master Key’
WHEN 19280 THEN ‘Primary Key’
WHEN 19283 THEN ‘ObfusKey’
WHEN 19521 THEN ‘Asymmetric Key Login’
WHEN 19523 THEN ‘Certificate Login’
WHEN 19538 THEN ‘Role’
WHEN 19539 THEN ‘SQL Login’
WHEN 19543 THEN ‘Windows Login’
WHEN 20034 THEN ‘Remote Service Binding’
WHEN 20036 THEN ‘Event Notification on Database’
WHEN 20037 THEN ‘Event Notification’
WHEN 20038 THEN ‘Scalar SQL Function’
WHEN 20047 THEN ‘Event Notification on Object’
WHEN 20051 THEN ‘Synonym’
WHEN 20549 THEN ‘End Point’
WHEN 20801 THEN ‘Adhoc Queries which may be cached’
WHEN 20816 THEN ‘Prepared Queries which may be cached’
WHEN 20819 THEN ‘Service Broker Service Queue’
WHEN 20821 THEN ‘Unique Constraint’
WHEN 21057 THEN ‘Application Role’
WHEN 21059 THEN ‘Certificate’
WHEN 21075 THEN ‘Server’
WHEN 21076 THEN ‘Transact-SQL Trigger’
WHEN 21313 THEN ‘Assembly’
WHEN 21318 THEN ‘CLR Scalar Function’
WHEN 21321 THEN ‘Inline scalar SQL Function’
WHEN 21328 THEN ‘Partition Scheme’
WHEN 21333 THEN ‘User’
WHEN 21571 THEN ‘Service Broker Service Contract’
WHEN 21572 THEN ‘Trigger on Database’
WHEN 21574 THEN ‘CLR Table-valued Function’
WHEN 21577
THEN ‘Internal Table (For example, XML Node Table, Queue Table.)’
WHEN 21581 THEN ‘Service Broker Message Type’
WHEN 21586 THEN ‘Service Broker Route’
WHEN 21587 THEN ‘Statistics’
WHEN 21825 THEN ‘User’
WHEN 21827 THEN ‘User’
WHEN 21831 THEN ‘User’
WHEN 21843 THEN ‘User’
WHEN 21847 THEN ‘User’
WHEN 22099 THEN ‘Service Broker Service’
WHEN 22601 THEN ‘Index’
WHEN 22604 THEN ‘Certificate Login’
WHEN 22611 THEN ‘XMLSchema’
WHEN 22868 THEN ‘Type’
ELSE ‘Hmmm???’
END AS ObjectType
FROM    [fn_trace_gettable](CONVERT(VARCHAR(150), ( SELECT TOP 1
FROM    [fn_trace_getinfo](NULL)
WHERE   [property] = 2
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
AND v.subclass_value = t.EventSubClass
WHERE   EventClass IN ( 46, 47, 164 )
– filter statistics created by SQL server
AND t.ObjectType NOT IN ( 21587 )
– filter tempdb objects
AND DatabaseID <> 2
– get only events in the past 24 hours
AND StartTime > DATEADD(HH, -24, GETDATE())
ORDER BY t.StartTime DESC ;
[1]: http://www.sqlservice.se/?s=default+trace
more ▼

answered Feb 01 '11 at 11:44 PM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

@Hakan - thats a great script, thanks for including it. I didnt realise Feodor is a colleague of yours - he did a nice blog about tweaking some code I wrote to use TypePerf to collect performance stats. Your employers are very lucky to have you two working there.

My blog - http://www.simple-talk.com/community/blogs/jonathanallen/archive/2010/12/20/96284.aspx
Feodor's blog - http://www.simple-talk.com/sql/performance/collecting-performance-data-into-a-sql-server-table/

Feb 02 '11 at 01:58 AM Fatherjack ♦♦
+1 @Fatherjack, thanks for sharing those links
Feb 02 '11 at 02:04 AM Cyborg
@Cyborg - no problem, its a bit off-topic but as I had mentioned them I thought it easier to include them than come back when someone asks for the details!
Feb 02 '11 at 03:58 AM Fatherjack ♦♦
@fatherjack, it was very resourceful!
Feb 02 '11 at 04:17 AM Cyborg

Comment to FatherJacks comment: Yes, we at SQL Service are very lucky to have Håkan and Feodor with us :-) We even have a few more of the same kind.

/Steinar Andersen, CEO SQL Service
Feb 07 '11 at 05:46 AM Steinar
(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 ..."

This doesn't always work. A developer came to me demanding to know who deleted 3 tables he had been using. I had tracing or Compliance manager running at the time ..... took a look ... turns out he deleted his own tables, and either forgot, or somehow didn't realize he'd done it.
more ▼

answered Oct 15 '10 at 07:15 AM

homebrew01 gravatar image

252 3 4 4

This was a dev box though, right? You don't let no steenking devs play on your prod boxes.
Oct 15 '10 at 07:19 AM WilliamD
(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 '10 at 08:15 AM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

(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.2k 72 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: 8507 times

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