Tuesday, 24 January 2012

Who dropped my SQL Database?

Today we found that one of our databases had mistakenly been dropped from our SQL server. To find out who you can use one of these two queries which look at the SQL log file trace...

DECLARE @path varchar(256)

SELECT @path = path
FROM sys.traces
where id = 1

SELECT *
FROM fn_trace_gettable(@path, 1)
where databasename = 'myDbName'
order by starttime desc

To prove it without a shadow of a doubt, you can use this more detailed query...

declare @databaseID int = 5 -- TODO: Figure out your database id here

;With cteObjectTypes AS
      (
      SELECT
            TSV.trace_event_id,
            TSV.subclass_name,
            TSV.subclass_value
      FROM
            sys.trace_subclass_values AS TSV JOIN
            sys.trace_columns AS TC ON
                  TSV.trace_column_id = TC.trace_column_id
      WHERE
            TC.[name] = 'ObjectType'
      ),
      cteEventSubClasses AS
      (
      SELECT
            TSV.trace_event_id,
            TSV.subclass_name,
            TSV.subclass_value
      FROM
            sys.trace_subclass_values AS TSV JOIN
            sys.trace_columns AS TC ON
                  TSV.trace_column_id = TC.trace_column_id
      WHERE
            TC.[name] = 'EventSubClass'
      )
SELECT
    TE.[name],
    I.ApplicationName,
      I.BigintData1,
      I.ClientProcessID,
      I.ColumnPermissions,
      I.DatabaseID,
      I.DatabaseName,
      I.DBUserName,
      I.Duration,
      I.EndTime,
      I.Error,
      I.EventSequence,
      Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
      I.FileName,
      I.HostName,
      I.IndexID,
      I.IntegerData,
      I.IsSystem,
      I.LineNumber,
      I.LoginName,
      I.LoginSid,
      I.NestLevel,
      I.NTDomainName,
      I.NTUserName,
      I.ObjectID,
      I.ObjectID2,
      I.ObjectName,
      Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
      I.OwnerName,
      I.ParentName,
      I.Permissions,
      I.RequestID,
      I.RoleName,
      I.ServerName,
      I.SessionLoginName,
      I.Severity,
      I.SPID,
      I.StartTime,
      I.State,
      I.Success,
      I.TargetLoginName,
      I.TargetLoginSid,
      I.TargetUserName,
      I.TextData,
      I.TransactionID,
      I.Type,
      I.XactSequence
FROM
    sys.traces T CROSS Apply
    sys.fn_trace_gettable(T.[path], T.max_files) I JOIN
    sys.trace_events AS TE ON
            I.EventClass = TE.trace_event_id LEFT JOIN
      cteEventSubClasses AS ESC ON
            TE.trace_event_id = ESC.trace_event_id And
            I.EventSubClass = ESC.subclass_value LEFT JOIN
      cteObjectTypes AS OT ON
            TE.trace_event_id = OT.trace_event_id AND
            I.ObjectType = OT.subclass_value
WHERE
    T.is_default = 1
    --TE.NAME = 'Object:Deleted'
    --and databaseid = @databaseID
    order by starttime desc

Safe to say with the latter query the evidence was pretty damning for one individual on our team Sad smile

No comments:

Post a Comment

Android mediaserver process killing battery life

If this happens to you, then do the following Force stop on Settings, Applications, All, Media Storage. Clear Cache and Clear Data, fo...