Another usage of Database Triggers is track Data Definition Language (DDL) changes that occur in a database. Today, I am going to enhance the AUTOS database script to add such auditing. This auditing is a very good practice when multiple people have syadmin rights to make such changes.
First, I am going to create a schema called Audit Database Tracking (ADT) to seperate the data tables from audit tracking tables.
|
1 2 3 4 5 6 7 8 9 |
<span style="color: #008000; font-size:small;">-- Delete existing schema. IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'ADT') DROP SCHEMA [ADT]; GO -- Create a ADT schema CREATE SCHEMA [ADT] AUTHORIZATION dbo; GO </span> |
Second, I am going to create a table to hold the keep track of the DDL changes that are occuring to the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
<span style="color: #008000; font-size:small;">/* Create schema level auditing - table. */ -- Remove table if it exists IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ADT].[LOG_DDL_CHANGES]') AND type in (N'U')) DROP TABLE [ADT].[LOG_DDL_CHANGES] GO -- Create a DDL Log Changes table CREATE TABLE [ADT].[LOG_DDL_CHANGES] ( [ChangeId] BIGINT IDENTITY(1,1) NOT NULL, [ChangeDate] [datetime] NOT NULL, [ChangeType] [varchar](20) NOT NULL, [ChangeBy] [nvarchar](256) NOT NULL, [AppName] [nvarchar](128) NOT NULL, [HostName] [nvarchar](128) NOT NULL, [SchemaName] [sysname] NULL, [ObjectName] [sysname] NULL, [Tsql] nvarchar(MAX) NULL, CONSTRAINT [pk_Ldc_ChangeId] PRIMARY KEY CLUSTERED ([ChangeId] ASC) ) GO -- Add defaults for key information ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_ChangeDate] DEFAULT (getdate()) FOR [ChangeDate]; ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_ChangeType] DEFAULT ('') FOR [ChangeType]; ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_ChangeBy] DEFAULT (coalesce(suser_sname(),'?')) FOR [ChangeBy]; ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_AppName] DEFAULT (coalesce(APP_NAME(),'?')) FOR [AppName]; ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_HostName] DEFAULT (coalesce(HOST_NAME(),'?')) FOR [HostName]; GO </span> |
Third, I am going to create a database trigger to capture the event data and add entries to the tracking table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
<span style="color: #008000; font-size:small;">/* Create schema level auditing - trigger. */ -- Remove trigger if it exists IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[TRG_TRACK_DDL_CHANGES]')) DROP TRIGGER [TRG_TRACK_DDL_CHANGES] GO -- Add the trigger to the db CREATE TRIGGER [TRG_TRACK_DDL_CHANGES] ON DATABASE FOR DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_DATABASE_SECURITY_EVENTS AS BEGIN -- Declare local variables DECLARE @VAR_DATA XML; DECLARE @VAR_SCHEMA sysname; DECLARE @VAR_OBJECT sysname; DECLARE @VAR_EVENT_TYPE sysname; -- Set local variables SET @VAR_DATA = EVENTDATA(); SET @VAR_EVENT_TYPE = @VAR_DATA.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'); SET @VAR_SCHEMA = @VAR_DATA.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'); SET @VAR_OBJECT = @VAR_DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') -- Do not track replication or statistics IF (( @VAR_EVENT_TYPE <> 'CREATE_STATISTICS') and ( @VAR_OBJECT not like 'syncobj_0x%')) BEGIN INSERT [ADT].[LOG_DDL_CHANGES] ([ObjectName], [ChangeType], [Tsql], [SchemaName]) VALUES ( CONVERT(sysname, @VAR_OBJECT), @VAR_EVENT_TYPE, @VAR_DATA.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), CONVERT(sysname, @VAR_SCHEMA) ); END END; </span> |
The EVENTDATA() function, only available for Server and Database DDL triggers, returns XML data is key to extracting information about the event. The FOR clause of the database trigger can narrow down what events you want to track. Last but not least, a database developer needs to be aware of other events such as replication that might not wanted to be tracked.
The image below shows what is captured in the audit table after a couple tables, triggers and stored procedures are created.
In summary, database triggers (AFTER) can be used to keep track of SCHEMA changes. Since the number of SCHEMA changes on a mature database are small, this is a great way to find out who did what when there is a production issue.
In SQL Server 2008, a built in Database Auditing mechanism was introduced. We will explore this feature in future articles.
