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

catwalkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.