Vampire (3/20/2009)

---

Thanks for the post Brian.

However, I am stuck with few issues. Hope you could throw some light on them and clarify .

1. I executed the Step to insert <local machine< into ValidIP table and still i am not able to connect from my local machine, bcoz of trigger execution.

2. I changed the trigger to capture the @IP value in troubleshooting the above said issue. Below is my code:

CREATE TRIGGER tr_logon_CheckIP

ON ALL SERVER

FOR LOGON

AS

DECLARE @IP NVARCHAR(15),@SqlCmd NVARCHAR(100);

SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));

INSERT INTO dbo.ValidIP (IP) VALUES (@IP);

BEGIN

IF IS_SRVROLEMEMBER('sysadmin') = 1

BEGIN

IF NOT EXISTS(SELECT IP FROM DBAWork.dbo.ValidIP WHERE IP = @IP)

ROLLBACK;

END;

END;

GO

But this is not inserting any @IP value in the ValidIP table. Does triggers have constraint of not inserting any value into the tables in execution.

3. If @IP value can't be inserted into the table ,as desired, is there any other way to see the output of EventData() or IP later.

Thanks in advance!!

[font="Comic Sans MS"]Vampire[/font]