As a developer or a DBA, you may have encountered situations where you needed to investigate who made certain changes in a SQL Server database. Questions like “Who dropped a table in the database?”, “Who altered the schema?”, or “Who created a database on the production server?” are common after a disaster. While larger companies often have auditing enabled and use specialized tools to capture such information, many DBAs and developers are unaware of a built-in feature called default trace.

The default trace is a background process that runs in every SQL Server installation. It captures various events related to configuration changes, login failures, object creation, object deletion, and more. By utilizing the default trace, you can easily track down the culprits behind these actions.

Let’s take a look at some of the events captured by the default trace:

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

As you can see, events like Object:Altered, Object:Created, and Object:Deleted can help you identify who made changes to the database objects. To retrieve the events of interest, you can use the following query:

SELECT * 
FROM::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\log.trc', DEFAULT)
WHERE EventClass IN (46, 47, 164)
ORDER BY StartTime DESC

In the above query, we are filtering for events with EventClass 46 (Object:Created), 47 (Object:Deleted), and 164 (Object:Altered). This will give you information about the database name, object name, hostname, application name, login name, and the start time of the event.

Additionally, you can also use the default trace to track database auto-growth events. The following query demonstrates how to find who dropped, created, or altered an object in the database:

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('C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\log.trc', DEFAULT)
WHERE EventClass IN (46, 47, 164)
    AND EventSubclass = 0
    AND DatabaseID <> 2
ORDER BY StartTime DESC

By leveraging the default trace, you can easily track down the individuals responsible for making changes to your SQL Server databases. This level of auditing can be crucial for maintaining data integrity and ensuring accountability within your organization.

So, the next time you need to investigate who dropped a table or altered a schema, don’t forget to utilize the power of the default trace in SQL Server.

Have you ever heard of someone being fired based on such auditing? Let us know in the comments below!