SQLKnowledge.com

Day by day Experience of a Microsoft SQL Server production DBA & Consultant..

Audit databases – using ddl triggers

Posted on | June 5, 2010 | No Comments

 

As a DBA, do you ever wonder what is going inside your production database? Who is changing database schema during production time without informing you. Now, you need not to worry, you can setup DDL triggers to easily records all changes in table; like what was changed, when and by whom etc. Given is two set of commands that you need to use and leave all worries aside. Also, you can prepare customized email alerts on top of data table to notify you as soon as an event occurs.

  1. Create a table that will store all records. For example I am using MSDB database to store this table.
  2. a DDL trigger need to created inside database; for that you want to enable audit. This trigger will capture all DDL events and save in table.
  3. Test and check process by creating test procedure, tables etc.

1. — Script to create table

USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DatabaseChangeLog](
    [LogId] [int] IDENTITY(1,1) NOT NULL,
    [DatabaseName] [varchar](256) NOT NULL,
    [EventType] [varchar](50) NOT NULL,
    [ObjectName] [varchar](256) NOT NULL,
    [ObjectType] [varchar](25) NOT NULL,
    [SqlCommand] [varchar](max) NOT NULL,
    [EventDate] [datetime] NOT NULL CONSTRAINT [DF_DBChangeLog_EventDate]  DEFAULT (getdate()),
    [LoginName] [varchar](256) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

clip_image002[4]

 
2.– Script to create database trigger

USE [SQLKnowledge]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [TrgDBChangeLog]
on database
for DDL_DATABASE_LEVEL_EVENTS
as
set nocount on
If Object_ID(‘msdb.dbo.DatabaseChangeLog’) IS NOT NULL
BEGIN
declare @data xml
set @data = EVENTDATA()
insert into msdb.dbo.DatabaseChangeLog(databasename, eventtype, objectname, objecttype, sqlcommand, loginname)
values(
@data.value(‘(/EVENT_INSTANCE/DatabaseName)[1]‘, ‘varchar(256)’),
@data.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘varchar(50)’),
@data.value(‘(/EVENT_INSTANCE/ObjectName)[1]‘, ‘varchar(256)’),
@data.value(‘(/EVENT_INSTANCE/ObjectType)[1]‘, ‘varchar(25)’),
@data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘varchar(max)’),
@data.value(‘(/EVENT_INSTANCE/LoginName)[1]‘, ‘varchar(256)’)
)
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [TrgDBChangeLog] ON DATABASE

clip_image004

3. — Script to test process and check Audit records

–Test table creation
use SQLKnowledge
create table test (fname varchar(10), lname varchar(10))

– Test procedure creation
create proc test1
as
select * from msdb.dbo.DatabaseChangeLog

– check table records in msdb database
select * from msdb.dbo.DatabaseChangeLog

clip_image006

Please leave a comment, if you have questions or need suggestions

Category: SQL DBA, Scripts, Uncategorized
Tags: > > > > > >

Comments

Leave a Reply