Incorrect EVENT_INSTANCE XML Schema when using DDL_LOGIN_EVENTS Trigger. by Paulo Condeça [MSFT]

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	286340
Opened:	7/6/2007 8:14:00 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Microsoft,

I was deploying a DDL Trigger, which objective is to send a message when someone executes a DROP_LOGIN, CREATE_LOGIN, ALTER_LOGIN in some DBs.
In the Event_Type is CREATE_LOGIN, the EVENT_INSTANCE XML Schema doesn't write the <TSQLCommand> Element, but it writes when the Event_Type is DROP LOGIN.
I guess this is a bug.

Best Regards,

Paulo Condeça.

Details (expand)

English

SQL Server 2005 SP2 - Enterprise Edition

SQL Engine

Win2003 Enterprise Server (SP1)

English

Create TRIGGER [DDL_1]
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
    DECLARE @data XML
    DECLARE @MailMessage nvarchar(100)
    SET @data = EVENTDATA()
        SET @MailMessage = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(100)')
        Print @MailMessage

If it is a CREATE LOGIN DDL, @MailMessage = NULL, if DDL is DROP @MailMessage = " The TSQL Command issued ".

@MailMessage = " The TSQL Command issued ", in both CREATE & DROP

0 attachments

Posted by Microsoft on 7/12/2010 at 3:51 PM

As a followup, this issue has been addressed in SQL Server 2008. Unlike 2005, if you use EVENTDATA in 2008, it will provide the TSQLCommand with the password obfuscated. E.g.,

<EVENT_INSTANCE>
<EventType>CREATE_LOGIN</EventType>
<PostTime>2010-07-12T15:18:50.910</PostTime>
<SPID>52</SPID>
<ServerName>server1</ServerName>
<LoginName>DOMAIN\account</LoginName>
<ObjectName>newuser</ObjectName>
<ObjectType>LOGIN</ObjectType>
<DefaultLanguage>us_english</DefaultLanguage>
<DefaultDatabase>master</DefaultDatabase>
<LoginType>SQL Login</LoginType>
<SID>RkycnY1oAk+m+WRAtuaIVA==</SID>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE LOGIN newuser WITH PASSWORD = '******'</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

Il-Sung.

Posted by Microsoft on 8/31/2009 at 12:52 PM

Hi,

The behavior that you are seeing is by design. The CREATE LOGIN DDL text can contain sensitive information, i.e., password, and hence the decision was made to omit the statement text.

Thanks,
Il-Sung.

Posted by Microsoft on 12/11/2007 at 11:23 AM

Hello,

Thank you for reporting this to us. We will investigate this and get back to you with the resolution.

Thanks,
-Vineet Rao.

Microsoft