How to Find Database Changes in SQL Server

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.