As a SQL Server professional, it is important to have a clear understanding of who created, dropped, or altered a database or its objects. This information can be crucial for troubleshooting, auditing, and maintaining the integrity of your SQL Server environment.

In a recent user group session, I posed this question to a group of young DBAs: “How can I know, who created/dropped/altered the database? How can I know, who created/dropped/altered the objects?” The responses I received ranged from DDL Triggers to Auditing, Error Logs, and Extended Events. While these answers were correct, I challenged them to find the easiest and simplest way to obtain this information without writing a single line of code.

One attendee surprised me with a response – “why not use Profiler?” This sparked my curiosity, and I decided to demonstrate an alternative method using SQL Server Management Studio – Schema Change History.

To access the Schema Change History report, navigate to the Server node in SQL Server Management Studio, right-click, select Reports, then Standard Reports, and finally “Schema Changes History”. This report retrieves information from the default trace, which we discussed in a previous post on the “Configuration Changes History” report. If the default trace is not enabled, the report will analyze each database and identify objects that were created or altered in the last 7 days.

Upon disabling the default trace, I discovered that the report still generated results in a different format. This intrigued me, and I investigated further.

With the default trace enabled, the report displays the following information:

SELECT *
FROM::fn_trace_gettable(@base_tracefilename, default)
WHERE EventClass IN (46, 47, 164)
AND EventSubclass = 0
AND DatabaseID <> 2

The EventClass values 46, 47, and 164 correspond to Object:Created, Object:Deleted, and Object:Altered respectively. By referencing the sys.trace_events table in MSDN, you can find more information about these event classes.

When the default trace is disabled, the report executes the following query in each database:

SELECT o.name AS OBJECT_NAME, o.type_desc, o.create_date, s.name AS schema_name
FROM sys.all_objects o
LEFT OUTER JOIN sys.schemas s ON (o.schema_id = s.schema_id)
WHERE create_date > (GETDATE() - 7);

The report generated by this query provides similar information, with a minor difference in the “create_date” and “modify_date” columns.

It is important to note that disabling the default trace prevents the report from capturing information about dropped databases. However, in most cases, the default trace is non-intrusive and provides valuable insights into database changes.

One caveat of the Schema Change History report is that if one database is inaccessible, it will result in an error and fail to report changes for the remaining databases. In such cases, you can manually run the T-SQL query mentioned above on the specific database to view the changes.

Have you ever disabled the Default Trace in your SQL Server environment? Have you utilized the Schema Change History report? I would love to hear about your experiences and learnings.