Using DDL triggers for auditing structure modification

SQL Server allows creating triggers for DDL operations. DDL (Data Definition Language) is the subset of the SQL language dealing with manipulation of structures, or metadata. The DDL keywords are CREATE, ALTER, and DROP. By placing triggers on DDL operations, you can audit the structural changes made on your server or in your databases. You can also block those changes within the trigger.

How to do it...

  1. Let's say that our goal is to audit security modifications in our databases. We want to centralize the audit in a dedicated database. We create the Audit database and te DDLAudit table in it:
    CREATE DATABASE Audit; GO USE Audit; CREATE TABLE dbo.DDLAudit ( DataBaseName sysname, EventType sysname, ...

Get Microsoft SQL Server 2012 Security Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.