Deny specific user from Remote Login to SQL Server

SQL Server Query to deny user on remote login feature, let user connect to database engine only from localhost (or 127.0.0.1).

This query below applies to SQL Server 2005 with SP2 and above higher version. It uses the SQL Server Logon Trigger

 

CREATE TRIGGER Deny_Remote_Login_Login1_trigger ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'Login1' AND (SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'Login1' --- Checking the given login and Host_Name not in ( 'SQLServerHostName') --- Connecting Remotely and Session_ID = @@SPID ) BEGIN PRINT 'RemoteLogin Attempt attempt of ' + ORIGINAL_LOGIN()+' denied, please login locally' ROLLBACK; END END;