Monday, July 20, 2009

Create DDL changes log

Its a common question, that How to trace changes made in database objects. Like,

Who has drooped my table ?
Who made changes in my view?
Who made changes in store procedure/Function?

You can solve this problem by creating a DDL (Data Definition Language) i.e. Create, Update, Drop trigger, to trace changes made in a database.

--==============CREATE table to store changes


CREATE TABLE [dbo].[ChangeLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()),
[LoginName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

go
--============= CREATE DATABASE TRIGGER TO INSERT CHANGES INTO dbo.changelog TABLE ===========
CREATE trigger backup_objects
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
as

set nocount on

declare @data xml
DECLARE @client_ip VARCHAR(15)
set @data = EVENTDATA()

SELECT @client_ip = client_net_address
FROM sys.dm_exec_connections
WHERE session_id =@data.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(256)')

insert into YOURDATABASE.dbo.changelog(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/SchemaName)[1]', 'varchar(256)') +'.'+
@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)')+'('+@client_ip+')'
)

4 comments:

  1. what about versioning?

    ReplyDelete
  2. Change the "ALTER" to "Create" in trigger.

    ReplyDelete
  3. I cannot get this to work, since the trigger does not have the rights to query sys.dm_exec_connections. What now?

    ReplyDelete
  4. Can you audit even the foreign key constraints or just a basic (create table, drop table & alter table)?

    ReplyDelete

All suggestions are welcome