Post reply

Using EVENTDATA()

  • August 4, 2008 at 3:33 pm

    #122629

    Hello Everyone

    I have created a DDL Trigger that will notify me when a user may be created in any of the databases. I am storing everything in a table. Here is the table that I am using to log everything

    2008-08-04 14:08:53.543 sa CREATE_LOGIN NULL

    2008-08-04 14:11:22.373 sa DROP_LOGIN DROP Login RLaszlo

    2008-08-04 14:13:00.547 sa CREATE_LOGIN NULL

    2008-08-04 14:16:27.797 sa DROP_LOGIN DROP Login RLaszlo

    2008-08-04 14:19:20.717 sa CREATE_LOGIN NULL

    2008-08-04 14:19:40.110 sa DROP_LOGIN DROP Login RLaszlo

    I need to know the command that created a login. As you can see, they are NULL

    Here is the syntax that I am using to capture the SQL command from the EVENTDATA()

    EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(2000)')

    Can anyone see anything incorrect? Same code works for the DROP Login, but will not supply the Create Login syntax

    Thanks

    Andrew SQLDBA

  • August 4, 2008 at 4:03 pm

    #852938

    Suggestion: Temporarily add an XML column to your table and store the entire EVENTDATA in there.

    Then you can query the actual EVENTDATA interactively, until you figure out what is wrong with your XML query.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 4, 2008 at 4:52 pm

    #852944

    Good Idea

    I will try that, at least I may be able to see what is in the XML string

    Thanks

    Andrew SQLDBA

  • August 5, 2008 at 9:28 am

    #853266

    Well, here is the problem, there is not a SQL Command in the Create Login, only on the Drop Login

    Create Login

    DROP Login

    That is a real bummer. How are we supposed to tell what login was just created?

    Andrew SQLDBA

  • August 5, 2008 at 9:49 am

    #853284

    The editor ate your XML Andrew. Attach them as files instead of inserting their contents.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply