Home Dashboard Directory Help
Search

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


Status: 

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


1
0
Sign in
to vote
Type: Bug
ID: 286340
Opened: 7/6/2007 8:14:00 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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
Sign in to post a comment.
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.
Sign in to post a workaround.