Is there any way to find who deleted the database

  • khanameerkhan

    SSC Veteran

    Points: 202

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

  • george sibbald

    SSC Guru

    Points: 104200

    should be in the default trace in your log directory.

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

  • Jack Corbett

    SSC Guru

    Points: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • khanameerkhan

    SSC Veteran

    Points: 202

    Thank You for your support.

    It is a very useful script it work for me

  • Jack Corbett

    SSC Guru

    Points: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • george sibbald

    SSC Guru

    Points: 104200

    ditto.

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

  • SQL Guy 1

    SSCoach

    Points: 15710

    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: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • JC-3113

    SSCrazy Eights

    Points: 8366

    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: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • JC-3113

    SSCrazy Eights

    Points: 8366

    Thanks Jack

    that is probably the issue

    have not dropped any databases this month

    have disabled some though

    jim

  • shaun.stuart

    SSCertifiable

    Points: 6660

    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: 15785

    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: 15785

    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: 993640

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 15 posts - 1 through 15 (of 17 total)

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