to keep the change log of Database activity for structure DDL trigger at Database level is used.
trigger code:
- 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.
- 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
