SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 EVENTDATA() Failing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chenn
Starting Member

1 Posts

Posted - 12/10/2012 :  13:55:05  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000