Thursday, October 25, 2012

How to block SSMS remotely

To block SSMS remotely :

There are few situations where you want to not allow users to connect to SQLServer from local machine or remotely.

How would i do that: it is very simple, you have to create a Trigger inorder to acomplish it.

 create following Trigger - ssms_remote_block1
        USE master;
GO
CREATE TRIGGER ssms_remote_block1
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT *
   FROM sys.dm_exec_sessions AS es
   WHERE
    es.login_name = ORIGINAL_LOGIN() AND -- The current login
    es.host_name <> 'CLWTD4000' AND -- Make sure it works only on remote connections
    es.program_name LIKE 'Microsoft SQL Server Management Studio%')
 ROLLBACK;
END;

if you want to disable trigger: disable trigger ssms_remote_block1 on all server

No comments:

Post a Comment