Who Dropped That Table in SQL Server? Three Ways and Their Limits

09:12 in the morning. The overnight report came back empty and a single line appeared in the team channel: "The Orders table is gone." Within minutes the second question arrives, and it is the hard one: who dropped it, and when? This article walks through which sources SQL Server can use to answer that today, what each source cannot see, and what has to be set up in advance so the answer is ready next time.

First: a Dropped Table or Deleted Rows?

The first job is to tell the two cases apart, because they leave traces in different places. DROP TABLE changes the schema: the table object disappears. DELETE or TRUNCATE TABLE removes data and the table stays. If the table is still there but empty, you are in the second case. Of the three ways below, the first sees only the first case; the other two can see both.

Way 1: the Default Trace

SQL Server runs a light trace in the background from installation. It records object create, drop and alter events together with the login, host name, application name and time. The "Schema Changes History" report in SQL Server Management Studio reads this same trace. First check that it is on and where its file lives:

SELECT id, path, max_size, max_files, start_time
FROM sys.traces
WHERE is_default = 1;

If no row comes back, the trace has been switched off and this way gives you no answer. If a row comes back, the query below lists events by table name. fn_trace_gettable starts from the file you give it and reads the rollover files after it; since sys.traces points at the newest file, the query takes the folder path and starts from the oldest file with log.trc. EventClass 46 is create, 47 is drop and 164 is alter. Each operation produces two rows (begin and commit); EventSubClass = 1 keeps only the committed one.

DECLARE @path nvarchar(260) =
  (SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX(N'\', REVERSE(path)), 260)) + N'log.trc'
   FROM sys.traces WHERE is_default = 1);

SELECT t.StartTime, e.name AS EventName, t.DatabaseName, t.ObjectName,
       t.LoginName, t.NTUserName, t.HostName, t.ApplicationName
FROM sys.fn_trace_gettable(@path, DEFAULT) AS t
JOIN sys.trace_events AS e ON e.trace_event_id = t.EventClass
WHERE t.EventClass IN (46, 47, 164)
  AND t.EventSubClass = 1
  AND t.ObjectName = N'Orders'
ORDER BY t.StartTime;

Its limits. The default trace rotates five files of 20 MB each; on a busy server that means a few hours of history, and if the event falls outside that window there is no trace. It holds only schema events: DELETE and TRUNCATE are not visible. A system administrator can switch it off with sp_configure 'default trace enabled', 0. Microsoft has announced the SQL Trace infrastructure as deprecated; it works today but should not be the basis of a new control.

Way 2: the Transaction Log

Every change is written to the transaction log first, and the begin record of each transaction carries the security identifier (SID) of the session that started it. The undocumented fn_dblog function reads these records. The query below returns delete and drop transactions with their time and login name:

SELECT [Transaction ID], [Begin Time], [Transaction Name],
       SUSER_SNAME([Transaction SID]) AS LoginName
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_BEGIN_XACT'
  AND [Transaction Name] IN ('DELETE', 'DROPOBJ', 'TRUNCATE TABLE')
ORDER BY [Begin Time];

To see which table a given transaction touched, look at the AllocUnitName column on the rows that share the same [Transaction ID].

Its limits. The function is undocumented and not supported by Microsoft; treat its output as a lead, not as the only evidence. The records survive only until the log is reused: in the simple recovery model that part can go after a checkpoint, in the full recovery model after a log backup. Scanning the log on a busy production server is heavy; run it on a restored copy where you can. The name it shows is the login: if the application account did the work, the application account is what you see, not a person.

Way 3: a SQL Server Audit Set Up in Advance

The first two ways search for traces after the event. A reliable answer comes from an audit set up before the event. SQL Server Audit has been available at database level in every edition since SQL Server 2016 SP1. The example below writes schema changes and every DELETE statement in the dbo schema, whoever runs it, to a file:

USE master;
CREATE SERVER AUDIT ChangeTrail
  TO FILE (FILEPATH = N'D:\Audit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 50)
  WITH (ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT ChangeTrail WITH (STATE = ON);

USE SalesDb;
CREATE DATABASE AUDIT SPECIFICATION SchemaAndDeletes
  FOR SERVER AUDIT ChangeTrail
  ADD (SCHEMA_OBJECT_CHANGE_GROUP),
  ADD (DELETE ON SCHEMA::dbo BY public)
  WITH (STATE = ON);

At the time of the incident the files are read like this. The client_ip and application_name columns exist from SQL Server 2017 on:

SELECT event_time, action_id, server_principal_name,
       client_ip, application_name, database_name, object_name, statement
FROM sys.fn_get_audit_file(N'D:\Audit\*.sqlaudit', DEFAULT, DEFAULT)
WHERE object_name = N'Orders'
ORDER BY event_time;

Things to watch. DELETE ... BY public covers everyone; the application's own deletes are written as well, so plan file size and retention from the start. ON_FAILURE = CONTINUE lets work carry on if the audit cannot be written; FAIL_OPERATION stops the work and SHUTDOWN stops the server. Which one to choose is an organisational decision. The database administrator should have no write or delete right on the folder holding the audit files; otherwise the trail is in the hands of the person being watched. Move the files regularly to your SIEM or to a separate store.

The Three Ways Side by Side

The Other Half of "Who"

All three ways tell you the same thing: which login, from which machine, at what time. That is half of the question. If the login is a shared application account, the person is still unknown. Even when the person is known, two questions stay open: did this work rest on a request, and had someone approved it? No database trace answers "why", because the reason lives outside the database, in a request or a conversation.

SQL Change Guard answers this second half only for work that goes through it. For a change run through the product, the request record carries who opened it, the justification, who approved it, the approved script text, the SQL account it ran under on the target, and the session number the target server reported. That session number and time make it possible to match the record against the audit trails above. A delete made outside the product, for example directly in SQL Server Management Studio, is neither seen nor blocked by the product; for that work the ways above apply.

Frequently Asked Questions

What can I do if the default trace is off?

For the past, look at the transaction log; if the records are still there, the login is in them. Going forward you can switch the trace back on with sp_configure 'default trace enabled', 1, but the real fix is to set up SQL Server Audit.

Does Azure SQL Database have the default trace?

No. SQL Trace is not supported in Azure SQL Database. There the same question is answered by Azure SQL Auditing, which also has to be switched on before the event.

Is a DDL trigger not enough?

A DDL trigger can write the DROP_TABLE event to a table and is a quick fix. But a privileged user can disable the trigger, and the table it writes to can be deleted. SQL Server Audit writes its file outside the database, which makes it stronger as evidence.

Does finding who did it bring the table back?

No. Finding who and getting the data back are separate jobs. In the full recovery model the table is recovered by a point in time restore to just before the event into a separate database, and copied back from there. See the related article for which question a backup answers and which it does not.