Audit SQL Server database schema changes

Keeping track of the changes made to your database objects is a key part of any SQL database security strategy or compliance policy including, among others, the Health Insurance Portability and Accountability Act, Sarbanes-Oxley, Payment Card Industry Data Security Standard or the European Union Data Protection Directive. However, even if your IT environment doesn’t have to comply to stringent security rules, being able to identify what database object has been changed, who has changed it as well as the exact time of the change, is invaluable in troubleshooting any schema related problems down the road, such as broken dependencies. So, how does one audit SQL Server schema changes?

One viable option is creating and maintaining DDL triggers for each of the database objects, which will fire whenever a database object is created, dropped or altered. A DDL triggers captures information on the EVENT that fired it using the EVENTDATA() function which returns the captured information in XML format. To set up schema auditing using DDL triggers:

1. Prepare the auditing infrastructure by creating a table which will hold the captured information. For example, if you want to capture an object change as well as the time the change was made and information on the login, host and application used to make the change, the table should have the following structure:

   CREATE TABLE Audit_Info
   (
          EventTime            DATETIME,
          LoginName            VARCHAR(255),
          UserName             VARCHAR(255),
          HostName             VARCHAR(255),
          ApplicationName      VARCHAR(255),
          DatabaseName         VARCHAR(255),
          SchemaName           VARCHAR(255),
          ObjectName           VARCHAR(255),
          ObjectType           VARCHAR(255),      
          DDLCommand           VARCHAR(MAX)
   )

2. Define the auditing scope. You can audit all schema changes in the database by specifying the ON DATABASE option, or on the entire SQL instance by specifying the ON ALL SERVER option
3. Create the DDL triggers. For instance, to audit changes made to database tables use the following:

   CREATE TRIGGER Audit_Table_DDL
   ON DATABASE
   FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
   AS
   DECLARE       @eventInfo XML
   SET           @eventInfo = EVENTDATA()
    
   INSERT INTO Audit_Info VALUES
   (
        REPLACE(CONVERT(VARCHAR(50),
               @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/UserName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/HostName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/ApplicationName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),
        CONVERT(VARCHAR(255),
             @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),
        CONVERT(VARCHAR(MAX),
             @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
   )

However, this approach comes with certain caveats. For instance, you can use DDL triggers to audit CREATE, DROP and ALTER TRIGGER statements, but a privileged user may issue the DISABLE TRIGGER command and effectively render auditing useless as DDL triggers can’t capture it; namely, a privileged user may change an audited object, then disable the DML triggers on the table which stored the captured changes, delete the rows which hold the details on the changed object and enable the DML triggers again. This way a malicious user can effectively wreak havoc on the database schema without being detected by the auditing system. Even if you are the only privileged SQL Server user, this kind of auditing system has the inherent disadvantage of not being able to report on any change made before it has been set in place. So, is there any effective way to audit SQL Server schema and object changes?

Fortunately, yes. The transaction log keeps a record of each change made to the database including information on when the change was made and who made it. The best part is – due to its nature, that information cannot be tampered with. But, there’s a catch – the transaction log isn’t humanly readable on its own. This is where ApexSQL Log comes into play

ApexSQL Log is an auditing and recovery tool for SQL Server databases which reads transaction logs, transaction log backups, detached transaction logs and database backups, and audits, reverts or replays data and object changes that have affected the database, including the ones that have occurred before the product was installed

To audit SQL Server schema and object changes with ApexSQL Log:

1. Start ApexSQL Log
2. Connect to the database

   

3. If you have any transaction log backups and/or detached transaction logs, click on the Add file button, select appropriate source files and click Next to advance through the wizard

   

4. Opt to the Open results in grid

5. In the Filter setup step of the wizard, use various filters to narrow down the results to a specific timeframe, operation, user, object or data row. Note: DDL operations are not audited by default, so if one wants to see them in the audited results, the same should be added through the Operations filter

   

The changes that meet the specified criteria, along with their details, will be listed in the application’s main grid. To narrow down the result set further, use the Grid filter in the left pane

In summary, if you want to ensure that no schema or object changes have been made to your SQL Server without your knowledge, examine its transaction log with ApexSQL Log

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.