EVENTDATA function behavior for DLL Triggers related to Logins

  • Article

I recently replied on a MSDN post regarding an issue where the user had created a DDL Trigger for ATER_LOGIN event but only wanted to be notified about password change events. I can understand the amount of frustration that can occur when you have mostly unwanted emails coming in from an alias for which you cannot setup an Outlook rule lest you miss out the actually *important* email. The trigger logic is as follows (Thanks to Ranjith for this one who replied earlier to the MSDN post):

CREATE TRIGGER MailTrigger

ON ALL SERVER

FOR ALTER_LOGIN

AS

DECLARE @command nvarchar(max)

 

SET @command = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')

 

IF CHARINDEX('password', @command) > 0

BEGIN

   -- mail logic

   PRINT 'MAIL'

END

ELSE

   PRINT 'NO MAIL'

GO

So, can this be accomplished?

Well Yes and No, depending on the version of SQL Server that you are using. On SQL Server 2008, you can actually use the EVENTDATA function in SQL Server to retrieve the actual T-SQL command. Once you do that, you will find that the mail logic that you have written will only send emails for commands which have the word PASSWORD in the T-SQLCommand node. The XML data would look something like this:

<EVENT_INSTANCE>

  <EventType>ALTER_LOGIN</EventType>

  <PostTime>2010-02-10T05:47:22.207</PostTime>

  <SPID>52</SPID>

  <ServerName><Server Name></ServerName>

  <LoginName>sa</LoginName>

  <ObjectName>user1</ObjectName>

  <ObjectType>LOGIN</ObjectType>

  <DefaultLanguage>us_english</DefaultLanguage>

  <DefaultDatabase>master</DefaultDatabase>

  <LoginType>SQL Login</LoginType>

  <SID><SID></SID>

  < TSQLCommand >

    < SetOptionsANSI_NULLS = "ON"ANSI_NULL_DEFAULT="ON"ANSI_PADDING="ON"QUOTED_IDENTIFIER="ON"ENCRYPTED="FALSE" />

    < CommandText > ALTER LOGIN user1 WITH PASSWORD=N'******' </ CommandText >

  </ TSQLCommand >

</EVENT_INSTANCE>

Notice that the password is masked out and you do not risk the actual password exposure and the DBA is also happy that he/she doesn’t get notified every time an ALTER LOGIN… is executed on the SQL instance.

Now you must be wondering if there is a catch somewhere? And yes there is! J Otherwise, I wouldn’t be writing this post! The EVENTDATA behavior in SQL Server 2005 was a bit different. Due to security concerns, the EVENTDATA function didn’t expose the T-SQL command for CREATE_LOGIN and ALTER_LOGIN events. This behavior is already logged as a Connect Item.

You will notice that the XML data returned for SQL Server 2005 doesn’t have the TSQLCommand node. So, what is the alternative? The SQL Server Profiler Trace event Audit Login Change Password is the answer. Setup a SQL Profiler trace (server side trace) with only this event. Read the trace file using fn_trace_gettable. The TextData column will give you the command similar to what the EVENTDATA function for SQL Server 2008 returns. Furthermore, you will find the Application Name, SPID, User Name and other specifics available in the other data columns in case you needed to hunt down the user! This particular profiler event has an event sub-class which has a specific meaning for each of the sub-class events. So, you can actually go a step further and define a SQL Server stored procedure which fetches the information from the SQL Server profiler trace, parses the input and then sends relevant emails if required.

 

Regards,

Amit Banerjee
Technical Lead, Microsoft SQL Server.