I Modified the trigger to send a mail once it is done. Sadly, it only runs on one database, I wish I could have this in a cursor, So i can apply it to all DB's on my server(s)
alter trigger SCHEMA_AUDIT on database
for CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_INDEX , ALTER_INDEX ,DROP_INDEX,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
ALTER_TABLE
as
begin
set nocount on
/*
Ref:
Name: DDL_TRIGGERS.sql
Version: 1.0
Author: Glen Wass
Date Created:22/05/2012
Description: create audit table and audit trigger to track stored procedure trigger index creation and changes
Impact:
Warnings:
Version History: DateVersionAuthorComments
---------------------------------------------------------------------------
22/05/2012|1.0| GW | Initial release
06/08/2012|1.1| GW| addded alter table
create table VEH_SCHEMA_LOG (id int identity,
USER_NAME nvarchar(255),
EVT_TYPE nvarchar (255),
OBJ_NAME nvarchar(255),
OBJ_TYPE nvarchar(255),
CREATE_DTTM datetime)
--- to stop users from altering the log table
DENY DELETE, UPDATE, ALTER on VEH_SCHEMA_LOG to [domain\DeptSqlUsers],
select * from veh_schema_log
*/
create table VEH_SCHEMA_LOG (id int identity,
USER_NAME nvarchar(255),
EVT_TYPE nvarchar (255),
OBJ_NAME nvarchar(255),
OBJ_TYPE nvarchar(255),
CREATE_DTTM datetime)
Declare @data xml
set @data=Eventdata()
Insert into VEH_SCHEMA_LOG values
(
@data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(255)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(255)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(255)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(255)'),
getdate()
)
set nocount off
end
declare @results varchar(max)
declare @UserName VARCHAR(150)
declare @HostName VARCHAR(50)
declare @subjectText varchar(max)
declare @objectName VARCHAR(255)
declare @objectType VARCHAR(255)
declare @eventType VARCHAR(255)
declare @body VARCHAR(max)
declare @bodytext VARCHAR(255)
declare @subject VARCHAR(255)
SELECT @UserName = SYSTEM_USER, @HostName = HOST_NAME()
SET @subjectText = 'DDL Modification on ' + @@SERVERNAME + ' by ' + @UserName
SET @results =
(SELECT @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @objectName = (SELECT @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(255)'))
SET @objectType = (SELECT @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'VARCHAR(255)'))
SET @eventType = (SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(255)'))
SET @body = 'User: '+@username+' from '+@hostname+' performed action of type ['+@eventType+'] on ['+@ObjectType+']:['+@objectName+'] at '+CONVERT(VARCHAR(20),GETDATE(),100)
--Send Mail when the trigger is called
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMail Profile',
@recipients = 'me@email.com',
@body = @body,
@subject = @subjectText,
@exclude_query_output = 1 --Suppress 'Mail Queued' message
GO