Post reply

Is there any way to find who deleted the database

  • September 20, 2010 at 6:39 am

    #224188

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

  • September 20, 2010 at 6:42 am

    #1223967

    should be in the default trace in your log directory.

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

  • 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

  • September 21, 2010 at 6:54 am

    #1224754

    Thank You for your support.

    It is a very useful script it work for me

  • 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

  • September 21, 2010 at 7:08 am

    #1224769

    ditto.

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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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.

  • 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.

  • 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)

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

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