Is there any way to find who deleted the database

• khanameerkhan

  Old Hand

  Points: 312

  More actions

  September 20, 2010 at 6:39 am

  #224188

  Is there any way to determine who deleted the database in sql server 2008.

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  September 20, 2010 at 6:42 am

  #1223967

  should be in the default trace in your log directory.

  ---------------------------------------------------------------------

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  September 20, 2010 at 1:46 pm

  #1224362

  Here's a query that will find it:

  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(CASE WHEN CHARINDEX('_', T.[path]) <> 0

  THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'

  ELSE T.[path]

  End, 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 AND

  TE.NAME = 'Object:Deleted'

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• khanameerkhan

  Old Hand

  Points: 312

  More actions

  September 21, 2010 at 6:54 am

  #1224754

  Thank You for your support.

  It is a very useful script it work for me

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  September 21, 2010 at 7:04 am

  #1224767

  khanameerkhan (9/21/2010)

  ---

  Thank You for your support.

  It is a very useful script it work for me

  Assuming that this is meant for me, you are welcome. If it isn't meant for me you are still welcome.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  September 21, 2010 at 7:08 am

  #1224769

  ditto.

  ---------------------------------------------------------------------

• SQL Guy 1

  SSCoach

  Points: 15979

  More actions

  September 21, 2010 at 8:32 am

  #1224878

  Hi Jack, I am also interested in this great script, but in my case I need it for tables, stored procedures and indexes. Should I change anything in this script ? And another question: does the database need to be in full recovery model ?

  Thanks

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  September 21, 2010 at 9:44 am

  #1224977

  That script returns any dropped objects. The ObjectType column tells you what type of object was dropped.

  No, the database does not need to be in the Full recovery model.

  You should also research event notifications as you can use them to notify you when an object is dropped in a specific database or on the server. Be careful because you will see the dropping of temp tables as well.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• JC-3113

  SSCrazy Eights

  Points: 8371

  More actions

  September 22, 2010 at 12:41 pm

  #1225849

  hi jack

  i am trying to use your script and have run it on three servers. All i am getting is 8227 object types

  which designate "(User-defined) Table". I do not see any databases being listed at all

  am i missing something here ?

  when i add this to your code at the bottom:

  TE.NAME = 'Object:Deleted' AND

  I.ObjectType != 8277;

  I get no entries

  thanks

  jim

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  September 22, 2010 at 1:02 pm

  #1225871

  If you aren't getting any errors then there haven't been any databases dropped in the time frame covered by the Default Trace. The default trace only has up to 100MB of data contained in up to 5 20MB files. So on your Server any database drops may have been before that time.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• JC-3113

  SSCrazy Eights

  Points: 8371

  More actions

  September 22, 2010 at 1:10 pm

  #1225878

  Thanks Jack

  that is probably the issue

  have not dropped any databases this month

  have disabled some though

  jim

• shaun.stuart

  SSCertifiable

  Points: 7068

  More actions

  September 23, 2010 at 10:52 am

  #1226463

  FYI, there is a nice routine I put on a couple of my servers that logs information like this so you don't have to worry about stuff falling out of the default trace. It works on SQL 2008 and 2005, although the 2005 version doesn't capture as much info.

  http://www.sqlservercentral.com/Forums/Topic947481-391-1.aspx

  Shaun

• Tara-1044200

  SSCoach

  Points: 15951

  More actions

  October 27, 2010 at 9:11 am

  #1242970

  I am into the same situation but the aboce script didnt help me find out the culprit. All of a sudden one of the database disappears which was available 3 weeks back,I am not sure if some tricked the trace file but i could not see the database now, please help.

• Tara-1044200

  SSCoach

  Points: 15951

  More actions

  October 27, 2010 at 10:27 am

  #1243041

  I do save trace files to a dataabse every 10 min but still i dont find any information for this particular database from the last 3 weeks, this is weired to me? May be some kind of auditing tools help find the reason.

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  October 28, 2010 at 3:07 am

  #1243402

  Jack Corbett (9/20/2010)

  ---

  Here's a query that will find it:

  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(CASE WHEN CHARINDEX('_', T.[path]) <> 0

  THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'

  ELSE T.[path]

  End, 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 AND

  TE.NAME = 'Object:Deleted'

  You need to write an "SQL Spackle" article on that...

  http://www.sqlservercentral.com/Forums/Forum2824-1.aspx

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)