Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Who deleted database

How can I find out who deleted a database?  I know it is not best practice to allow multiple users  sysadmin access or dbo access.  

I have tried the code below and it did not tell me.

Any other suggestions?

DECLARE @path varchar(256) 
 
SELECT @path = path 
FROM sys.traces 
where id = 1 
 
SELECT TextData, HostName, ApplicationName, LoginName, StartTime, DatabaseName 
FROM fn_trace_gettable(@path, 1) 
where TextData is not null 
order by startTime desc

Open in new window

0
catwalk
Asked:
catwalk
1 Solution
 
mikeewaltonCommented:
This script will return any dropped objects, including temp tables as long as it's still contained in the default trace. The ObjectType column tells you what type of object was dropped. It does concern me that the query you posted did not return any results, it should have.
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'

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now