August 12, 2008 at 10:11 am
One of our clients complained that some vendors they had just entered a couple of weeks ago were no longer in the drop down list. After some investigation I found the table had been created on 07/30/08. My backup of the database to my reports server showed the table creation date should have been 05/14/2005. The suspicion is someone deleted either the table or some data and tried to cover it up by recreating the table from an older copy on the staging server dated 06/10/2008 which did not include the latest vendors. SO, what is the way to track down who did it? I am the new dba so please explain the long way!:w00t:
August 12, 2008 at 11:05 am
You could try querying the default trace, if it was running (it's enabled by default, but can be disabled). Here's a query that will find actions that will show actions against objects in a particular database (set the @dbname variable to the database name):
/*
Use this section only if running query about objects WITHIN a database, such as
drops and adds of tables, etc. Helps with join to sys.objects */
USE PropertyTax -- modify db name
go
declare @dbname sysname
set @dbname = 'Propone' -- modify db name
/* ------------------------------------------------------------------------------- */
-- obtain file name for Default Trace
declare @TraceFileName nvarchar(256)
set @TraceFileName = (select path from sys.traces where is_default = 1)
-- get general name for Default Trace (remove rollover number)
set @TraceFileName =
substring (@Tracefilename, 1, (charindex ('\log_', @tracefilename) + 3)) + '.trc'
-- sample query: get info about recently added, deleted and modified
-- stored procedures in a database
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(@TraceFileName, 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 > '2008-06-30 11:00'
and databaseID = db_id(@dbname)
and eventclass in (46, 47, 164) -- object created, deleted or altered
and eventsubclass = 1 -- only committed act
Greg
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply