On a QA server I need to give access to specific login from just one IP to log to this server.
I wrote This login trigger
Create TRIGGER tr_CheckIP ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @IPAddress NVARCHAR(50) ;
If ORIGINAL_LOGIN()='test'
--print ORIGINAL_LOGIN()
begin
SET @IPAddress = convert(NVARCHAR(50),ConnectionProperty('client_net_address'))
--print @IPAddress
IF NOT EXISTS ( SELECT IP
FROM master..IPAddress
WHERE IP = @IPAddress )
BEGIN
ROLLBACK
END
End
END
However the trigger is preventing the user from log although the IP exists in the IPAddress table.
Any idea where in the problem in the trigger