LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon