Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  SQL Server Administration (2008)  EVENTDATA() Failing

Author  Topic  Chenn Starting Member 1 Post Posted - 2012-12-10 : 13:55:05 Interesting issue... I'm creating a login trigger to audit logins. The problem I have revolves around the EVENTDATA() function and a single user. We have a user that logs in from another server via python. When the trigger references EVENTDATA() the login for that user fails. Are there permissions or something? I included the trigger below - some commented code for troubleshooting. This trigger works (maybe not as is, but I've been hacking at it trying to figure out what the issues is). create TRIGGER LOGON_TRG ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN DECLARE @xml XML DECLARE @loginName VARCHAR(255) SET @xml = EVENTDATA() -- this command causes a certain login to fail --set @loginName = @xml.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') insert into MON_EVENTS.DBO.LOGON (Post_Time, event_type, SPID, server_name, login_name, login_type, sid, client_host, is_pooled) values ('01-01-2012', 'test', 1, 'test', 'test', 'test', 2, 'test', 3) /*( EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(max)'), EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)'), EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)'), EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(max)'), EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)'), EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]','nvarchar(max)'), EVENTDATA().value('(/EVENT_INSTANCE/sid)[1]','nvarchar(max)'), EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)'), EVENTDATA().value('(/EVENT_INSTANCE/IsPooled)[1]','nvarchar(max)') ) declare @dt date set @dt = getdate() */ end go