DDL trigger

to keep the change log of Database activity for structure DDL trigger at Database level is used.

1. I ran across some permission issue: where  it is needed to make sure that owner of db where log table resided and source db should be same.
2. for user's activities to be logged he needs to be member of destination or EventLogDb. (Public Role is Ok)

trigger code:

CREATE TRIGGER [Backup_Objects]

on database

--WITH EXECUTE AS SELF

for

CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,

CREATE_TABLE, ALTER_TABLE, DROP_TABLE,

CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,

CREATE_ROLE,ALTER_ROLE,DROP_ROLE,

CREATE_SCHEMA,ALTER_SCHEMA,DROP_SCHEMA,

CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,

CREATE_VIEW,ALTER_VIEW,DROP_VIEW,

CREATE_USER,ALTER_USER,DROP_USER

as

--Created on Agt 11th 2011 by Abhishek Joshi

--refer:http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes

SET NOCOUNT ON

declare @data xml

set @data = EVENTDATA()

insert into eventslogdb.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/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)')

)

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO