How to know - who deleted the table?

Question

Post reply

How to know - who deleted the table?

venu_ksheerasagaram

SSCommitted

Points: 1911
More actions
August 4, 2009 at 8:06 am

#140362

Hi Everybody,
I am having a Database in which there are 20 people are having the rights to delete the tables etc...
Yesterday i found that one table is missing from the list, It is a important table. Is there any way to find out the user who was deleted the table?
Thankyou all,
Venu Gopal.K
Software Engineer
INDIA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Katmai Stratovolcano Say Hey Kid Points: 676 More actions · Answer 1

Katmai Stratovolcano

Say Hey Kid

Points: 676

August 4, 2009 at 8:22 am

#1034275

Just have a look at the SQL server Logs

Lowell SSC Guru Points: 323518 More actions · Answer 2

the default trace would have any objects that were dropped/created/altered.

try this query:

select * from sys.trace_events

declare @tracefile varchar(256)

SELECT @tracefile=convert(varchar(256),value)

from(

SELECT *

FROM fn_trace_getinfo(default)) X

where property=2

print @tracefile

SELECT * from fn_trace_gettable(@tracefile, default)

select

ev.name

,tr.StartTime

,tr.DatabaseID

,tr.DatabaseName

,tr.ObjectID

,tr.ObjectName as 'Trace ObjectName'

,o.name as 'Sysobjects Name'

,o.type_desc

,tr.ObjectType

/* see 'ObjectType Trace Event Column' in BOL to get a translation of these type codes */

,sv.subclass_name as 'ObjectAbbrv'

,tr.LoginName

,tr.HostName

,tr.ApplicationName

from fn_trace_gettable(@tracefile, default) tr

join sys.trace_events ev

on tr.eventclass = ev.trace_event_id

join sys.trace_subclass_values sv

on tr.eventclass = sv.trace_event_id

and tr.ObjectType = sv.subclass_value

and sv.trace_column_id = 28

left join sys.objects o

on tr.ObjectID = o.object_id

where starttime > '2009-08-01 01:00'

-- and databaseID = db_id(@dbname)

and eventclass in (46, 47, 164) -- object created, deleted or altered

and eventsubclass = 1 -- only committed act

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared SSC Guru Points: 260824 More actions · Answer 3

If you have DDL logging (from triggers), you can use that.

If you have a DDL trace running, you can use that.

If you have a log parser, you can use that.

That's in order of increasing difficulty for finding the data you're looking for.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

steveb. SSC-Forever Points: 46733 More actions · Answer 4

Or you can try the GUI way (if you have the default trace running)

Right Click Datase --> Reports --> Standard Reports --> Schema Changes History

I hope that you have a clean backup..

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

I would suggest the default trace as well. There's an article here to help:

http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/