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
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...
- 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
Auditdatabase and te
DDLAudittable in it:
CREATE DATABASE Audit; GO USE Audit; CREATE TABLE dbo.DDLAudit ( DataBaseName sysname, EventType sysname, ...