While reading a blog post regarding using server DDL triggers to capture a “CREATE DATABASE” and fire an email to the DBA, I thought that coupling a trigger to sp_send_dbmail and an external executable wasn’t a great idea. I decided that there must be a better way to capture that important event. For me, this would really only occur in a development server where the developers often have enhanced rights to the dev servers. Production is pretty tight. However, my heart is always broken when a developer runs up and asks me to restore an accidentally dropped database on the Dev server, only for me to find out that the same developer created that database without telling me. My backup packages don’t dynamically grab all the online databases when it runs, so if they add a database and don’t tell me, it don’t get backed up.
My first thought was Extended Events. I don’t know much about them, I’ve heard the abstracts and read the rumors, but I haven’t had call to work with them. If I remember correctly, it seemed there was a way to capture the CREATE Database event, but then I was stuck with a data record of the event, and not a handy way to email it to me. Searching for Extended Events Send Email took me quickly to a Johnathan Kehayias awesome answer on the MSDN forums explaining that, no, there’s no real plumbing between Extended Events and the Service Broker. His second answer in the same discussion linked to his blog which is fountain of knowledge and an article explaining Event Notifications vs Extended Events. From there, I saw a link to another article in his blog. Sounded pretty darn close to what I wanted.
That lead to my current version of a generic Event Notification system which I am currently testing on a number of servers. It’s actually a combination of procedures from Jonathan’s articles and Sergey Maskalik’s article. Sergey’s error handling and timeout on the Waitfor along with cleanup of the Conversation Handles coupled with Jonathan’s shredding of the XML message body seems to be a work of art to me. I added in some of my own magic to ensure XACT_ABORT was on, some COALESCE’s to ensure a null value wasn’t concatenated over valid values, and setting this up in a “utility” database and setting “TRUSTWORTHY ON” to allow the execution of sp_send_dbmail in MSDB.
We’ll start with the guts needed to set up and wire up the Service Broker and Queues, Services, and Routes. It’s pretty boiler plate, with the added commands to turn on the Service Broker and set TRUSTWORTHY.
NOTE: Jonathan has visited and brought up a very valid and real security risk. In my approach, I take a utility database and set TRUSTWORTHY=ON to it. Please visit and read Raul Garcia’s article on the risks of the TRUSTWORTHY bit. At the time of my writing this, the database that I deploy this solution to is already restricted to the DBA team and anyone with Sysadmin privileges. In this case, all those principals already have enough access to do what this setting allows without any other work, so I feel the risk in my environment is low. For a more secure solution, I strongly recommend a careful review of your situation, and indeed using certificates to sign the procedures to allow cross-database execution.
USE [master]; go -- SET EVERYTHING UP WITH THE SERVICE BROKER -- We could also do this when creating the DatabaseBackup database -- as part of the initial package run, or update. -- Trustworthy allows a stored proc in the current database -- execute SP_SEND_DBMAIL in msdb ALTER DATABASE DatabaseBackup SET ENABLE_BROKER; go ALTER DATABASE DatabaseBackup SET TRUSTWORTHY ON; go USE DatabaseBackup GO -- Drop the notification if it exists IF EXISTS ( SELECT * FROM sys.server_event_notifications WHERE name = N'CaptureDBAEvents' ) BEGIN DROP EVENT NOTIFICATION [CaptureDBAEvents] ON SERVER; END -- Drop the route if it exists IF EXISTS ( SELECT * FROM sys.routes WHERE name = N'DBAEventRoute' ) BEGIN DROP ROUTE [DBAEventRoute]; END -- Drop the service if it exists IF EXISTS ( SELECT * FROM sys.services WHERE name = N'DBAEventService' ) BEGIN DROP SERVICE [DBAEventService]; END -- Drop the queue if it exists IF EXISTS ( SELECT * FROM sys.service_queues WHERE name = N'DBAEventQueue' ) BEGIN DROP QUEUE [DBAEventQueue]; END IF EXISTS ( SELECT * FROM MASTER.sys.event_notifications WHERE name = N'CaptureDBAEvents' ) BEGIN DROP EVENT NOTIFICATION [CaptureDBAEvents] ON SERVER END -- Create a service broker queue to hold the events CREATE QUEUE [DBAEventQueue] WITH STATUS=ON; GO -- Create a service broker service receive the events CREATE SERVICE [DBAEventService] ON QUEUE [DBAEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]); GO -- Create a service broker route to the service CREATE ROUTE [DBAEventRoute] WITH SERVICE_NAME = 'DBAEventService', ADDRESS = 'LOCAL'; GO -- Create the event notification to capture the events CREATE EVENT NOTIFICATION [CaptureDBAEvents] ON SERVER WITH FAN_IN FOR CREATE_DATABASE, DROP_DATABASE, CREATE_LOGIN, DROP_LOGIN, CREATE_USER, DROP_USER, BLOCKED_PROCESS_REPORT, DEADLOCK_GRAPH, ADD_ROLE_MEMBER, ADD_SERVER_ROLE_MEMBER TO SERVICE 'DBAEventService', 'current database'; GO
Right above, while creating the EVENT NOTIFICATION, you can see the event types I have. I decided that while it’s great to have AutoGrowth events sent, that in our current environment, this might be more noise than there is value for, so we have left that out for now. Sure there’s a lot more audit events that I could hit up too, but I felt that the ROLL MEMEBERSHIPS, USER and LOGIN work and the DATABASE create and drop were a great start. Also the DEADLOCK_GRAPH was just a nice freebie.
Next, the guts of this, an Stored Procedure that is generic enough to handle different Event Types and shred as much of the XML as possible into a friendly email message. Right, who doesn’t like a mailbox full of raw XML in the morning? 🙂 Note, the final ELSE in the shredding and Email Body building, so if we decided to add a Event Type, we will always just email off the XML until things are fixed.
USE DatabaseBackup GO -- Drop the procedure if it exists IF EXISTS ( SELECT * FROM sys.procedures WHERE name = N'ProcessEvents' ) BEGIN DROP PROCEDURE [ProcessEvents]; END GO CREATE PROCEDURE [dbo].[ProcessEvents] WITH EXECUTE AS OWNER AS SET XACT_ABORT ON; DECLARE @eventType VARCHAR(128); DECLARE @messagetypename NVARCHAR(256); DECLARE @ch UNIQUEIDENTIFIER; DECLARE @serverName VARCHAR(128); DECLARE @postTime VARCHAR(128); DECLARE @databaseName VARCHAR(128); DECLARE @duration VARCHAR(128); DECLARE @growthPages INT; DECLARE @userName VARCHAR(128); DECLARE @loginInfo VARCHAR(256); DECLARE @SID VARCHAR(128); DECLARE @messageBody XML; DECLARE @emailTo VARCHAR(50); DECLARE @emailBody VARCHAR(MAX); DECLARE @subject varchar(150); SET @emailTo = '<DBA TEAM EMAIL HERE>@gmail.com; WHILE (1=1) BEGIN BEGIN TRY BEGIN TRANSACTION WAITFOR ( RECEIVE TOP(1) @ch = conversation_handle, @messagetypename = message_type_name, @messagebody = CAST(message_body AS XML) FROM DBAEventQueue ), TIMEOUT 60000; IF (@@ROWCOUNT = 0) BEGIN ROLLBACK TRANSACTION; BREAK; END IF (@messagetypename = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification') BEGIN -- Get the common information SELECT @eventType = COALESCE(@messagebody.value('(/EVENT_INSTANCE/EventType)','varchar(128)'),'UNKNOWN'), @serverName = COALESCE(@messagebody.value('(/EVENT_INSTANCE/ServerName)','varchar(128)'),'UNKNOWN'), @postTime = COALESCE(CAST(@messagebody.value('(/EVENT_INSTANCE/PostTime)','datetime') AS VARCHAR),'UNKNOWN'); SELECT @emailBody = 'The following event occurred:' + CHAR(10) + CAST('Event Type: ' AS CHAR(25)) + @EventType + CHAR(10) + CAST('ServerName: ' AS CHAR(25)) + @ServerName + CHAR(10) + CAST('PostTime: ' AS CHAR(25)) + @PostTime + CHAR(10); -- Now the custom XML fields depending on the Event Type IF (@EventType like '%_FILE_AUTO_GROW') BEGIN SELECT @duration = COALESCE(@messagebody.value('(/EVENT_INSTANCE/Duration)','varchar(128)'),'UNKNOWN'), @growthPages = COALESCE(@messagebody.value('(/EVENT_INSTANCE/IntegerData)', 'int'),'UNKNOWN'), @databaseName = COALESCE(@messagebody.value('(/EVENT_INSTANCE/DatabaseName)','varchar(128)'),'UNKNOWN'); SELECT @emailBody = @emailBody + CAST('Duration: ' AS CHAR(25)) + @Duration + CHAR(10) + CAST('GrowthSize_KB: ' AS CHAR(25)) + CAST(( @GrowthPages * 8 ) AS VARCHAR(20)) + CHAR(10) + CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName + CHAR(10); END ELSE IF (@EventType like '%_DATABASE') BEGIN SELECT @userName = COALESCE(@messageBody.value('/EVENT_INSTANCE/LoginName', 'varchar(128)'),'UNKNOWN'), @DatabaseName = COALESCE(@messagebody.value('(/EVENT_INSTANCE/DatabaseName)','varchar(128)'),'UNKNOWN'); SELECT @emailBody = @emailBody + CAST('User: ' AS CHAR(25)) + @userName + CHAR(10) + CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName + CHAR(10); END ELSE IF (@EventType like '%_LOGIN') BEGIN SELECT @userName = COALESCE(@messageBody.value('/EVENT_INSTANCE/LoginName', 'varchar(128)'),'UNKNOWN'), @loginInfo = COALESCE(@messageBody.value('/EVENT_INSTANCE/ObjectName', 'varchar(256)'),'UNKNOWN'), @SID = COALESCE(@messageBody.value('/EVENT_INSTANCE/SID', 'varchar(128)'),'UNKNOWN'); SELECT @emailBody = @emailBody + CAST('User: ' AS CHAR(25)) + @userName + CHAR(10) + CAST('New User: ' AS CHAR(25)) + @loginInfo + CHAR(10) + CAST('New SID: ' AS CHAR(25)) + @SID + CHAR(10); END ELSE IF (@EventType like '%_ROLE_MEMBER') BEGIN DECLARE @roleName VARCHAR(128); DECLARE @command VARCHAR(128); SELECT @userName = COALESCE(@messageBody.value('/EVENT_INSTANCE/LoginName', 'varchar(128)'),'UNKNOWN'), @loginInfo = COALESCE(@messageBody.value('/EVENT_INSTANCE/ObjectName', 'varchar(256)'),'UNKNOWN'), @roleName = COALESCE(@messageBody.value('/EVENT_INSTANCE/RoleName', 'varchar(256)'),'UNKNOWN'), @command = COALESCE(@messageBody.value('/EVENT_INSTANCE/TSQLCommand/CommandText', 'varchar(256)'),'UNKNOWN'); SELECT @emailBody = @emailBody + CAST('User: ' AS CHAR(25)) + @userName + CHAR(10) + CAST('Affected User: ' AS CHAR(25)) + @loginInfo + CHAR(10) + CAST('New Role: ' AS CHAR(25)) + @roleName + CHAR(10) + CAST('Command issued: ' AS CHAR(25)) + @command + CHAR(10); END ELSE -- TRAP ALL OTHER EVENTS AND SPIT OUT JUST THE XML - We can pretty it up later :) BEGIN SELECT @emailBody = CAST(@messagebody AS VARCHAR(max)); END -- Send email using Database Mail SELECT @subject = @eventType + ' on ' + @serverName; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA Email', -- your defined email profile @recipients = @emailTo, -- your email @subject = @subject, @body = @emailBody; END IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error') BEGIN DECLARE @errorcode INT; DECLARE @errormessage NVARCHAR(3000) ; -- Extract the error information from the sent message SET @errorcode = (SELECT @messagebody.value( N'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"; (/brokerns:Error/brokerns:Code)', 'int')); SET @errormessage = (SELECT @messagebody.value( N'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"; (/brokerns:Error/brokerns:Description)', 'nvarchar(3000)')); -- Log the error END CONVERSATION @ch WITH CLEANUP; END IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog') BEGIN -- End the conversation END CONVERSATION @ch WITH CLEANUP; END COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; DECLARE @ErrorNum INT; DECLARE @ErrorMsg NVARCHAR(3000); SELECT @ErrorNum = ERROR_NUMBER(), @ErrorMsg = ERROR_MESSAGE(); -- log the error BREAK; END CATCH END GO
Finally, let’s activate the new Stored Procedure by altering the Queue. Again this is pretty boiler plate.
-- Activate the procedure with the Queue ALTER QUEUE [DBAEventQueue] WITH STATUS=ON, ACTIVATION (STATUS=ON, PROCEDURE_NAME = [ProcessEvents], MAX_QUEUE_READERS = 1, EXECUTE AS OWNER); GO
Thanks, I hope that helps anyone interested in Event Notifications.
It is so easy to use a certificate to sign the activation procedure and not open the security risk of TRUSTWORTHY = ON, so I am wondering why you would take that route and not show people the right way of doing this. The article you referenced mentioned that crossing databases should use a certificate to sign the procedure, and you can find all the code and information on this in my other article:
It would be better to create the Notification and Procedure in msdb than to set TRUSTWORTHY = ON because it doesn’t open up a new security issue.
Jonathan, thanks for responding, and you’re right, setting TRUSTWORTH=ON can be a security risk. This article http://blogs.msdn.com/b/sqlsecurity/archive/2007/12/03/the-trustworhy-bit-database-property-in-sql-server-2005.aspx seems to sum up the risks rather well. In my case, I’m deploying this to a utility database that only the DBA team has access to. Anyone who could get access to this database already has all the permissions they would need to bypass other security on the server.
I understand your larger concern that having my article out there can pose to the general Google searcher, so I’ll add some wording in my article to try to point out the risks involved, and refer back to your cert article and Sergey’s as well.
Thank you very much for sharing your knowledge.
I just like to ask you if it’s normal to still see one record in sys.conversation_endpoints in the state of CONVERSING, even after the e-mail is send.
This records remains in sys.conversation_endpoints until I clean it up manually.
Could you please give me a feedback?
Thank you very much.
Hi Frank. I just checked a number of my servers, and they all have a single row in sys.conversation_endpoints. I’ll do some testing, perhaps sending an “END CONVERSATION” is needed inside the logic.
Hi JFay, thank you very much for your reply.
I’ll wait for you results.
The conversation is reused by event notifications to improve performance. Setting up and tearing down a conversation for each message significantly reduces the performance of Service Broker, by as much as 4-10 times (http://rusanu.com/2007/04/25/reusing-conversations/) . I don’t manually cleanup the conversations in my examples online because it’s not supposed to be closed out for event notifications for every message.
Thanks for stopping in and clarifying Jonathan. I’ll leave my code as is, I’ve done some checking on my instances here, and I only see at most 4 rows in the conversation_endpoints view, so I don’t see any harm. I’ll watch this for a while, and if it just continues to grow, look to review the process.
Mr. Denny wrote about this problem some time ago: “Sep 11 2008 11:00AM GMT ”
“…Apparently this is a known issue which they are working on…”
Do you think it may be the same problem?
Just came across this while commenting on someone else’s blog, looking for someone else’s code doing the same thing. Nice to see someone else doing EN – I’m hoping 2013 will finally be the year for it. FWIW, I’ve got a similar setup, rolled out to 40 servers. Feel free to take a look! http://thebakingdba.blogspot.com/2012/10/master-of-all-i-survey-using-event.html
I have followed the above process to get any event alert emails.But I do not receive any.I have a mail profile which is fine but not sure it’s a problem with the broker. Do I need to do anything else rather than running above three scripts?
I executed above three scripts and then to test it created a new database.But nothing came to my email..Could you advice on thi?
Pingback: The Road to Awareness (aka Event Notifications) | Paul Winans, At Your Service
Thank you very much for sharing your knowledge.
I m just wondering if you create a didicated database, in your case DatabaseBackup,for this job.
cause it work for me just when i create a new emty database.
After reading your article feeling more confident and energetic, thanks for your example which you describing inside article, today is my first visit onyour blog and here i read some informative article, great article.