Jonathan,
Wasn't able to find your email link on the blog.. but here is the code..
as you notice that I have commented out most majority of the elements to be emailed ..
I have a suspicion, as I mentioned before, on the AUDIT_DATABASE_MANAGEMENT_EVENT
and here is the version
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
-- Using msdb prevents the need for certificate signing the
-- activation procedure to execute sp_send_dbmail across
-- databases
USE [msdb];
GO
-- Drop the notification if it exists
IF EXISTS ( SELECT *
FROM sys.server_event_notifications
WHERE name = N'CaptureAuditDatabaseManagementEvents' )
BEGIN
DROP EVENT NOTIFICATION CaptureAuditDatabaseManagementEvents ON SERVER;
END
-- Drop the route if it exists
IF EXISTS ( SELECT *
FROM sys.routes
WHERE name = N'AuditDatabaseManagementEventRoute' )
BEGIN
DROP ROUTE AuditDatabaseManagementEventRoute;
END
-- Drop the service if it exists
IF EXISTS ( SELECT *
FROM sys.services
WHERE name = N'AuditDatabaseManagementEventService' )
BEGIN
DROP SERVICE AuditDatabaseManagementEventService;
END
-- Drop the queue if it exists
IF EXISTS ( SELECT *
FROM sys.service_queues
WHERE name = N'AuditDatabaseManagementEventQueue' )
BEGIN
DROP QUEUE AuditDatabaseManagementEventQueue;
END
-- Create a service broker queue to hold the events
CREATE QUEUE [AuditDatabaseManagementEventQueue]
WITH STATUS=ON;
GO
-- Create a service broker service receive the events
CREATE SERVICE [AuditDatabaseManagementEventService]
ON QUEUE [AuditDatabaseManagementEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
-- Create a service broker route to the service
CREATE ROUTE [AuditDatabaseManagementEventRoute]
WITH SERVICE_NAME = 'AuditDatabaseManagementEventService',
ADDRESS = 'LOCAL';
GO
-- Create the event notification to capture the events
CREATE EVENT NOTIFICATION [CaptureAuditDatabaseManagementEvents]
ON SERVER
WITH FAN_IN
FOR AUDIT_DATABASE_MANAGEMENT_EVENT
TO SERVICE 'AuditDatabaseManagementEventService', 'current database';
GO
-- ==========================================================================================
USE [msdb];
GO
SELECT
ApplicationName =message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')
,DatabaseName=message_body.value('(/EVENT_INSTANCE/DatabaseName )[1]','nvarchar(32)')
,DBUserName=message_body.value('(/EVENT_INSTANCE/DBUserName )[1]','nvarchar(32)')
,EventSubClass=message_body.value('(/EVENT_INSTANCE/EventSubClass )[1]','int' )
, (case message_body.value('(/EVENT_INSTANCE/EventSubClass )[1]','int' ) when 1 then 'Create' when 2 then 'Alter' when 3 then 'Drop' when 4 then 'Dump' when 11 then 'Load' else 'Undefined' end) SubClassNameDesc
,HostName=message_body.value('(/EVENT_INSTANCE/HostName )[1]','nvarchar(32)')
,LoginName=message_body.value('(/EVENT_INSTANCE/LoginName )[1]','nvarchar(32)')
,NTDomainName=message_body.value('(/EVENT_INSTANCE/NTDomainName )[1]','nvarchar(32)')
,NTUserName=message_body.value('(/EVENT_INSTANCE/NTUserName )[1]','nvarchar(32)')
,ObjectName=message_body.value('(/EVENT_INSTANCE/ObjectName )[1]','nvarchar(32)')
,OwnerName=message_body.value('(/EVENT_INSTANCE/OwnerName )[1]','nvarchar(32)')
,ServerName=message_body.value('(/EVENT_INSTANCE/ServerName )[1]','nvarchar(32)')
,SessionLoginName=message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')
,StartTime=message_body.value('(/EVENT_INSTANCE/StartTime )[1]','datetime2' )
,Success =message_body.value('(/EVENT_INSTANCE/Success )[1]','int' )
,TextData=message_body.value('(/EVENT_INSTANCE/TextData )[1]','nvarchar(32)')
FROM ( SELECT CAST(message_body AS XML) AS message_body
FROM [AuditDatabaseManagementEventQueue]
) AS Tab;
-- ==========================================================================================
-- Create the Activation Stored Procedure to Process the Queue
IF EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[USP_ProcessAuditDatabaseManagementEvents]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents];
GO
create PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents]
WITH EXECUTE AS OWNER
AS
DECLARE @message_body XML;
DECLARE @message_sequence_number INT;
DECLARE @dialog UNIQUEIDENTIFIER;
DECLARE @email_message NVARCHAR(MAX);
WHILE ( 1 = 1 )
BEGIN
BEGIN TRANSACTION;
-- Receive the next available message FROM the queue
WAITFOR
(
RECEIVE TOP(1) -- just handle one message at a time
@message_body=CAST(message_body AS XML)
FROM dbo.AuditDatabaseManagementEventQueue
), TIMEOUT 1000; -- if queue empty for 1 sec, give UPDATE AND GO away
-- If we didn't get anything, bail out
IF ( @@ROWCOUNT = 0 )
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
declare
@ApplicationNamenvarchar(32)--nvarchar
,@DatabaseNamenvarchar(32)--nvarchar
,@DBUserNamenvarchar(32)--nvarchar
,@EventSubClassint--int
,@HostNamenvarchar(32)--nvarchar
,@LoginNamenvarchar(32)--nvarchar
,@NTDomainNamenvarchar(32)--nvarchar
,@NTUserNamenvarchar(32)--nvarchar
,@ObjectNamenvarchar(32)--nvarchar
,@OwnerNamenvarchar(32)--nvarchar
,@ServerNamenvarchar(32)--nvarchar
,@SessionLoginNamenvarchar(32)--nvarchar
,@StartTimenvarchar(32)--datetime
,@Successint--int
,@TextDatanvarchar(32)--ntext
select
@ApplicationName=message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')
,@DatabaseName=message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(32)')
,@DBUserName=message_body.value('(/EVENT_INSTANCE/DBUserName)[1]','nvarchar(32)')
,@EventSubClass=message_body.value('(/EVENT_INSTANCE/EventSubClass)[1]','int' )
,@HostName=message_body.value('(/EVENT_INSTANCE/HostName)[1]','nvarchar(32)')
,@LoginName=message_body.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(32)')
,@NTDomainName=message_body.value('(/EVENT_INSTANCE/NTDomainName)[1]','nvarchar(32)')
,@NTUserName=message_body.value('(/EVENT_INSTANCE/NTUserName)[1]','nvarchar(32)')
,@ObjectName=message_body.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(32)')
,@OwnerName=message_body.value('(/EVENT_INSTANCE/OwnerName)[1]','nvarchar(32)')
,@ServerName=message_body.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(32)')
,@SessionLoginName=message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')
,@StartTime=message_body.value('(/EVENT_INSTANCE/StartTime)[1]','nvarchar(32)')
,@Success=message_body.value('(/EVENT_INSTANCE/Success)[1]','int' )
,@TextData=message_body.value('(/EVENT_INSTANCE/TextData)[1]','nvarchar(32)')
-- Generate formatted email message
select
@email_message = 'Audit DB Mgmt Event Occured::'
--+ char(10) + 'ApplicationName'+@ApplicationName
--+ char(10) + 'DatabaseName'+@DatabaseName
--+ char(10) + 'DBUserName'+@DBUserName
--+ char(10) + 'EventSubClass'+(case @EventSubClass when 1 then 'Create' when 2 then 'Alter' when 3 then 'Drop' when 4 then 'Dump' when 11 then 'Load' else 'Undefined' end)
--+ char(10) + 'HostName'+@HostName
--+ char(10) + 'LoginName'+@LoginName
--+ char(10) + 'NTDomainName'+@NTDomainName
--+ char(10) + 'NTUserName'+@NTUserName
--+ char(10) + 'ObjectName'+@ObjectName
--+ char(10) + 'OwnerName'+@OwnerName
--+ char(10) + 'ServerName'+@ServerName
--+ char(10) + 'SessionLoginName'+@SessionLoginName
--+ char(10) + 'StartTime'+@StartTime
--+ char(10) + 'Success'+@Success
--+ char(10) + 'TextData'+@TextData
-- Send email using Database Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'LocalServer', -- your defined email profile
@recipients = 'JohnE.SQL@gmail.com', -- your email
@subject = 'Audit Database Management Event Notification',
@body = @email_message;
-- Commit the transaction. At any point before this, we could roll
-- back. The received message would be back on the queue AND the
-- response wouldn't be sent.
COMMIT TRANSACTION;
END
GO
-- Alter the queue to use the activation procedure
ALTER QUEUE [AuditDatabaseManagementEventQueue]
WITH STATUS=ON,
ACTIVATION
(STATUS=ON,
PROCEDURE_NAME = [dbo].[USP_ProcessAuditDatabaseManagementEvents],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER);
GO
Cheers,
John Esraelo