Determine which user deleted a SQL Server database?

Question

I have a SQL Server 2005 database that has been deleted, and I need to discover who deleted it. Is there a way of obtaining this user name?

Thanks, MagicAndi.

Answer 1

If there has been little or no activity since the deletion, then the out-of-the-box trace may be of help. Try running:

DECLARE @path varchar(256)

SELECT @path = path
FROM sys.traces
where id = 1

SELECT *
FROM fn_trace_gettable(@path, 1)

[In addition to the out-of-the-box trace, there is also the less well-known 'black box' trace, which is useful for diagnosing intermittent server crashes. This post, SQL Server’s Built-in Traces, shows you how to configure it.]

Answer 2

I would first ask everyone who has admin access to the Sql Server if they deleted it.

Answer 3

The best way to retrieve the information is to restore the latest backup.

Now to discuss how to avoid such problems in the future.

First make sure your backup process is running correctly and frequently. Make transaction log baclup evey 15 mintues or half an hour if it is a higly transactional database. Then the most you lose is a half an hour's worht of work. Practice restoring the database until you can easily do it under stress.

In SQL Server 2008 you can add DDL triggers (not sure if you can do this in 2005) which allow you to log who did changes to structure. It might be worth your time to look into this.

Do NOT allow more than two people admin access to your production database - a dba and a backup person for when the dba is out. These people should load all changes to the database structure and code and all of the changes should be scripted out, code reviewed and tested first on QA. No unscripted, "run by the seat of your pants" code should ever be run on prod.

Answer 4

Here is bit more precise TSQL

SELECT DatabaseID,NTUserName,HostName,LoginName,StartTime
FROM 
sys.fn_trace_gettable(CONVERT(VARCHAR(150), 
        ( SELECT TOP 1
                    f.[value]
            FROM    sys.fn_trace_getinfo(NULL) f
            WHERE   f.property = 2
        )), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE TE.trace_event_id =47 AND T.DatabaseName = 'delete'
-- 47 Represents event for deleting objects. 

This can be used in the both events of knowing or not knowing the database/object name. Results look like this: