0

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

4
  • 1
    Firewalls are usually really good for filtering on IP addresses. Feb 12, 2016 at 7:23
  • @kin thanks, the other post what i was looking for. i am in a problem now this trigger today is preventing any connection even as SA ..
    – sebeid
    Feb 12, 2016 at 15:38
  • @sebeid are you using the script that you posted or that one that I linked ?
    – Kin Shah
    Feb 12, 2016 at 16:02
  • @Kin the one you posted is working fine .. i am talking about the script i posted .. but i was able to fix it i connected from SQLcmd and disable the trigger .. thanks for considerations
    – sebeid
    Feb 12, 2016 at 16:04

0

Browse other questions tagged or ask your own question.