SQL SERVER – Who Dropped Table or Database?

I used to run a series on Hero’s and Sherlock Holmes is one of my favorite. There is always a need to get to the bottom of the problem statement and whether you are a developer or a DBA, at least once you might have been asked one of the following “Who” question after a disaster.

  • Who dropped table in the database? From which application? When?
  • Who dropped database? What was the date and time?
  • Who created database on production server?
  • Who altered the database?
  • Who dropped the schema?
  • Who altered the schema?

And there are many other similar questions. Generally rich companies have auditing enabled and they have many tools which might be capturing all DDLs via either Auditing or via 3rd party tools.  Many of DBAs and developer don’t know that there is a default trace which runs in the background in every SQL Server installation

Here are few usage of default traces which are via SSMS.

SQL SERVER – SSMS: Configuration Changes History

Solarwinds

SQL SERVER – SSMS: Schema Change History Report

Let’s look at the events captured by the default trace.

SELECT DISTINCT Trace.EventID, TraceEvents.NAME AS Event_Desc
FROM   ::fn_trace_geteventinfo(1) Trace
,sys.trace_events TraceEvents
WHERE Trace.eventID = TraceEvents.trace_event_id

Here is what we would get in SQL Server 2014

Event_IDEvent_Desc
18Audit Server Starts And Stops
20Audit Login Failed
22ErrorLog
46Object:Created
47Object:Deleted
55Hash Warning
69Sort Warnings
79Missing Column Statistics
80Missing Join Predicate
81Server Memory Change
92Data File Auto Grow
93Log File Auto Grow
94Data File Auto Shrink
95Log File Auto Shrink
102Audit Database Scope GDR Event
103Audit Schema Object GDR Event
104Audit Addlogin Event
105Audit Login GDR Event
106Audit Login Change Property Event
108Audit Add Login to Server Role Event
109Audit Add DB User Event
110Audit Add Member to DB Role Event
111Audit Add Role Event
115Audit Backup/Restore Event
116Audit DBCC Event
117Audit Change Audit Event
152Audit Change Database Owner
153Audit Schema Object Take Ownership Event
155FT:Crawl Started
156FT:Crawl Stopped
164Object:Altered
167Database Mirroring State Change
175Audit Server Alter Trace Event
218Plan Guide Unsuccessful

As we can see there are various interesting events. Object:Altered, Object:Created and Object:Deleted can help us in identifying who dropped, altered or created any object. Once we get event class ID for the event of interest, we can use below query and replace the Event class ID.

-- read all available traces.
DECLARE @current VARCHAR(500);
DECLARE @start VARCHAR(500);
DECLARE @indx INT;
SELECT @current = path
FROM sys.traces
WHERE is_default = 1;
SET @current = REVERSE(@current)
SELECT @indx = PATINDEX('%\%', @current)
SET @current = REVERSE(@current)
SET @start = LEFT(@current, LEN(@current) - @indx) + '\log.trc';
-- CHNAGE FILER AS NEEDED
SELECT *
FROM::fn_trace_gettable(@start, DEFAULT)
WHERE EventClass IN (92,93) -- growth event
ORDER BY StartTime DESC

In above, I have used EventClass 92 and 93 to track database auto-growth events. Here is the query to find who dropped / created or altered object in database or database itself.

-- read all available traces.
DECLARE @current VARCHAR(500);
DECLARE @start VARCHAR(500);
DECLARE @indx INT;
SELECT @current = path
FROM sys.traces
WHERE is_default = 1;
SET @current = REVERSE(@current)
SELECT @indx = PATINDEX('%\%', @current)
SET @current = REVERSE(@current)
SET @start = LEFT(@current, LEN(@current) - @indx) + '\log.trc';
-- CHNAGE FILER AS NEEDED
SELECT CASE EventClass
WHEN 46 THEN 'Object:Created'
WHEN 47 THEN 'Object:Deleted'
WHEN 164 THEN 'Object:Altered'
END, DatabaseName, ObjectName, HostName, ApplicationName, LoginName, StartTime
FROM::fn_trace_gettable
(@start, DEFAULT)
WHERE EventClass IN (46,47,164) AND EventSubclass = 0 AND DatabaseID <> 2
ORDER BY StartTime DESC

Have you ever heard of someone being fired based on such auditing?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – How to View Objects in mssqlsystemresource System Database?
Next Post
Interview Question of the Week #036 – What is the difference between @@DATEFIRST and SET DATEFIRST?

Related Posts

12 Comments. Leave new

Leave a Reply

Menu