Trigger to block host ips

-- This will create a trigger to stop host ips to connect sql server




CREATE TRIGGER tr_logon_hostname_blacklist

ON

ALL SERVER WITH EXECUTE AS 'sa'

FOR LOGON

AS

BEGIN

DECLARE @ClientHost nvarchar(max);

SELECT @ClientHost = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)');

-- ClientHost gives IP except if the connecting to SQL Server from instance machine.

-- Do NOT put '' in this otherwise you will block client access from instance machine.

IF @ClientHost IN ('10.168.178.71','10.168.178.7','10.168.178.8','10.168.178.4','10.168.178.70')

ROLLBACK;

END;





-- To drop the trigger.



DROP TRIGGER tr_logon_hostname_blacklist ON ALL SERVER;