SQL Server: find who created or dropped a database
Use the default trace to find the login and host that created or dropped a database.
SQL Server's default trace can tell who created or dropped a database: it records the database creation (event class 46) and drop (event class 47) events, with the login and the host. This method is based on a third-party article (SQL Server: find which user deleted the database).
1. Find the default trace file
SELECT
path AS [Default Trace File]
,max_size AS [Max File Size of Trace File]
,max_files AS [Max No of Trace Files]
,start_time AS [Start Time]
,last_event_time AS [Last Event Time]
FROM sys.traces WHERE is_default = 1
GO
2. Load the trace into a table
Replace the path with the value of the Default Trace File column.
USE tempdb
GO
IF OBJECT_ID('dbo.TraceTable', 'U') IS NOT NULL
DROP TABLE dbo.TraceTable;
SELECT * INTO TraceTable
FROM ::fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL\Log\log_12.trc', default)
GO
3. List the creations and drops of the database
SELECT
DatabaseID
,DatabaseName
,LoginName
,HostName
,ApplicationName
,StartTime
,CASE
WHEN EventClass = 46 THEN 'Database Created'
WHEN EventClass = 47 THEN 'Database Dropped'
ELSE 'NONE'
END AS EventType
FROM tempdb.dbo.TraceTable
WHERE DatabaseName = 'AppDb'
AND (EventClass = 46 /* Event Class 46 refers to Object:Created */
OR EventClass = 47) /* Event Class 47 refers to Object:Deleted */
GO
Notes
- Event class 46 is
Object:Createdand event class 47 isObject:Deleted. For a database, they correspond to its creation and its drop. TheLoginNamecolumn gives the login that ran the statement. - The default trace rolls over a limited number of files, so an old event may no longer be present. The number of retained files is shown by
Max No of Trace Files.