t92Y51Uz

1USE master
2GO
3-- Create table to hold valid IP values
4CREATE TABLE ValidIPAddress (IP NVARCHAR(15)
5CONSTRAINT PK_ValidAddress PRIMARY KEY)
6
7
8-- Declare local machine as valid one
9INSERT INTO ValidIPAddress
10SELECT '<local machine>'
11-- Create Logon Trigger to stop logins from invalid IPs
12CREATE TRIGGER tr_LogOn_CheckIP ON ALL SERVER
13    FOR LOGON
14AS
15    BEGIN
16        DECLARE @IPAddress NVARCHAR(50) ;
17        SET @IPAddress = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]',
18                                           'NVARCHAR(50)') ;
19        IF NOT EXISTS ( SELECT  IP
20                        FROM    master..ValidIPAddress
21                        WHERE   IP = @IPAddress ) 
22            BEGIN
23            -- If login is not a valid one, then undo login process
24                SELECT  @IPAddress
25                ROLLBACK --Undo login process
26            END
27
28
29    END