Determine which user deleted the SQL Server database?

Question

I have a SQL Server 2005 database that has been deleted, and I need to find out who deleted it. Is there any way to get this username?

Thanks, MagicAndi.

Answer 1

If there has been little or no activity since the deletion, tracing out of the box can be useful. Try to run:

DECLARE @path varchar(256)

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

SELECT *
FROM fn_trace_gettable(@path, 1)

[In addition to the finished trace, there is also a lesser known black box trace, which is useful for diagnosing intermittent server failures. This post, Embedded SQL Server Traces , shows you how to configure it.]

Answer 2

, Sql-, .

Answer 3

- .

, .

, . baclup evey 15 , . . , .

SQL Server 2008 DDL ( , 2005 ), , . , .

- dba , dba . , , QA. , " " .

Answer 4

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. 

/. :