Create a trigger to stop users from accessing Database from SQL Server Management Studio

Create a trigger to stop users from accessing Database from SQL Server Management Studio. 

This is a simple method where you can stop someone from connecting to a Database using SQL Server management studio. This helps DBA,s to combat Application admins accessing database using application user name password which in some cases is  not encrypted in connection string. 

CREATE TRIGGER [TR_LOGON_APP] ON ALL SERVER FOR LOGON AS BEGIN DECLARE @program_name nvarchar(128) DECLARE @host_name nvarchar(128) 
SELECT 
  @program_name = program_name, 
  @host_name = host_name 
FROM 
  sys.dm_exec_sessions AS c 
WHERE 
  c.session_id = @@spid IF ORIGINAL_LOGIN() IN('loginName') 
  AND @program_name LIKE '%Management%Studio%' BEGIN RAISERROR(
    'This login is for application use only.', 
    16, 1
  ) ROLLBACK;
END END;
GO 

ENABLE TRIGGER [TR_LOGON_APP] ON ALL SERVER 

GO