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
DECLARE @IP NVARCHAR(15),@SqlCmd NVARCHAR(100);
SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)', 'NVARCHAR(15)'));
INSERT INTO dbo.ValidIP (IP) VALUES (@IP);
IF IS_SRVROLEMEMBER('sysadmin') = 1
IF NOT EXISTS(SELECT IP FROM DBAWork.dbo.ValidIP WHERE IP = @IP)
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!!