Log Who Drops Table in SQL Server Database with DDL Trigger

SQL Server database administrator can log dropped tables for auditing to find out who drop database table using DDL triggers on database level for DROP_TABLE event. SQL logs for table drop events can be used to identify the SQL user who dropped a specific database table and when to figure out details for auditing.

A SQL Server DDL trigger can be created to fire every time a table is dropped in a SQL database using Drop Table command. SQL DDL trigger provides detailed information related with Drop Table statement execution using EVENTDATA

First we can create a custom log table which basically stores SQL Server Extended Event data with a simple structure as seen in below Create Table command.


Create Table DropTableCommandLogs (

 EventType sysname,

 PostTime datetime,

 LoginName sysname,

 UserName sysname,

 DatabaseName sysname,

 SchemaName sysname,

 ObjectName sysname,

 ObjectType sysname,

 CommandText nvarchar(max)

)

Above log table or custom audit table contains only data which will be retrieved from the DDL trigger which is executed after DROP TABLE command. We will soon talk about the DDL trigger. Before, if you want to add any other information you can add additional fields into the log table.

We used to work triggers which are executed after INSERT, UPDATE and DELETE commands which we call DML statements (Data Manipulation Language commands). Microsoft SQL Server has introduced also DDL triggers (Data Definition Language) which are executed immediately after a DDL command like Create Table, Drop Database and other statements which effect database objects in other words metadata about database objects rather than data itself.

Let's create SQL Server DDL trigger on table drop command in our sample database as follows


Create Trigger ddlTrigger_LogDropTable

On Database For DROP_TABLE

As



Insert Into DropTableCommandLogs (

 EventType,

 PostTime,

 LoginName,

 UserName,

 DatabaseName,

 SchemaName,

 ObjectName,

 ObjectType,

 CommandText

)

Select

 EventType = EVENTDATA().value('(EVENT_INSTANCE/EventType)[1]', 'sysname'),

 PostTime = EVENTDATA().value('(EVENT_INSTANCE/PostTime)[1]', 'datetime'),

 LoginName = EVENTDATA().value('(EVENT_INSTANCE/LoginName)[1]', 'sysname'),

 UserName = EVENTDATA().value('(EVENT_INSTANCE/UserName)[1]', 'sysname'),

 DatabaseName = EVENTDATA().value('(EVENT_INSTANCE/DatabaseName)[1]', 'sysname'),

 SchemaName = EVENTDATA().value('(EVENT_INSTANCE/SchemaName)[1]', 'sysname'),

 ObjectName = EVENTDATA().value('(EVENT_INSTANCE/ObjectName)[1]', 'sysname'),

 ObjectType = EVENTDATA().value('(EVENT_INSTANCE/ObjectType)[1]', 'sysname'),

 CommandText = EVENTDATA().value('(EVENT_INSTANCE//TSQLCommand[1]/CommandText)[1]', 'nvarchar(max)')



GO

DDL trigger which is used to log "Drop Table" commands executed on a SQL Server database

SQL Server DDL trigger to log Drop Table command execution

Let's make an example to demonstrate and see how DROP TABLE commands are logged in to our custom logging or auditing table


-- Create Table SampleTable(id tinyint)

Drop Table SampleTable

Who dropped database table in SQL Server

Let's now query log table DropTableCommandLogs to see how data is logged


select * from DropTableCommandLogs

SQL log table for table drop DDL events

As seen in above screenshot, DDL trigger provides EventData details like type of the event, event execution time, the login name and the user name who has executed the DDL statement, database name, schema name and the object name (in our case this is the table name which is dropped), object type. One more information is provided into the log table from DDL trigger which is the CommandText. CommandText from the EventData shows which SQL command caused DDL trigger to fire. Since we have created our DDL trigger only for DROP_TABLE, we will always see the DROP TABLE command in this field with the exact statement executed by the user.

The login name field contains either the Windows login names and also the SQL login names.