How to Find Database Changes in SQL Server

Native Auditing vs. Netwrix Auditor for SQL Server
{{ firstError }}
We care about security of your data. Privacy Policy
Native Auditing Netwrix Auditor for SQL Server
Native Auditing
Netwrix Auditor for SQL Server
Steps
  1. Run MS SQL Management Studio → Connect to the database you want to audit → Click "New Query" → Copy the following code into the "New Query" box:

DECLARE @RC int, @TraceID int, @on BIT
EXEC @rc = sp_trace_create @TraceID output, 2, N'C:\path\file'
SELECT RC = @RC, TraceID = @TraceID
-- Follow Common SQL trace event list and common sql trace
-- tables to define which events and table you want to capture
SELECT @on = 1
EXEC sp_trace_setevent @TraceID, 128, 1, @on
-- (128-Event Audit Database Management Event, 1-TextData table column)
EXEC sp_trace_setevent @TraceID, 128, 11, @on
EXEC sp_trace_setevent @TraceID, 128, 14, @on
EXEC sp_trace_setevent @TraceID, 128, 35, @on 
EXEC @RC = sp_trace_setstatus @TraceID, 1
GO

  1. Define the file trace location (marked red) and hit "Execute" to start a new trace.
  2. Execute this query to stop the trace when you want to audit data:

sp_trace_setstatus @traceid = 1, @status = 0

  1. Execute this query in order to import the trace into a database table:

USE DBname
SELECT * INTO tablename FROM ::fn_trace_gettable('C:\path\file.trc', DEFAULT)
GO

  1. Execute this query in order to view top 1000 rows of trace data:

SELECT TOP 1000 [TextData] ,[HostName] ,[LoginName] ,[StartTime] ,[EndTime] ,[ServerName] ,[EventClass]
FROM [DBname].[dbo].[tablename]
WHERE DBname.dbo.tablename.TextData IS NOT NULL

  1. Inspect the "TextData" column for the events: CREATE DATABASE, DROP DATABASE, ALTER DATABASE in order to know what database was changed. To find out who changed it and when it was changed, refer to the "LoginName" and "StartTime" columns respectively.
how to find database changes in sql server: T-SQL report
  1. Run Netwrix Auditor → Navigate to "Search" → Click on "Advanced mode" if not selected → Set up the following filters:
    • Filter = "Data source"
      Operator = "Equals"
      Value = "SQL Server"
    • Filter = "Object type"
      Operator = "Equals"
      Value = "Database"
    • Filter = "Action"
      Operator = "Equals"
      Value = "Modified"
  2. Click the "Search" button and review what changes were made to databases.
how to find database changes in sql server: Netwrix Auditor Interactive Search result

Detect Database Changes across Your SQL Server before Critical Data is Put at Risk

Why do we need to find database changes in SQL Server? Because any unauthorized SQL Server database change, such as the deletion, modification or creation of a database or its objects, can jeopardize your data security. For example, deletions or modifications can result in data loss or system unavailability. Even creation of new databases can spell trouble, because it reduces the amount of free space on the server. 

Netwrix Auditor for SQL Server facilitates reliable database change tracking and auditing, including detection of schema changes, data changes and more. You’ll no longer have to spend hours setting up queries in Transact-SQL, for instance, to identify who changed tables. Netwrix Auditor for SQL Server offers predefined and customizable dashboards and reports that deliver all the critical details about each database change in an easy-to-read format. On top of that, the Interactive Search feature enables you to get to the root cause of a problem in minutes, and alerts on critical activity ensure you’ll be the first to know about possible threats. 

Related How-tos