Using Logon Trigger to exclude SSMS connections

• lbrady

  SSC Eights!

  Points: 883

  More actions

  October 28, 2013 at 2:01 pm

  #299668

  Hi all,

  Somehow, the application ID/PW became known to certain individuals within my organization. Someone ran a rogue query against the database via SSMS which brought down production! We cannot change the Application ID/PW because of the magnitude of such an effort.

  I thought a logon trigger would take care of someone using the application ID to query the database.

  I created the following CREATE TRIGGER [Prevent_ApplicationID]

  ON ALL SERVER

  FOR LOGON

  AS

  BEGIN

  IF ORIGINAL_LOGIN() = 'test' AND

  (select count(*) FROM sys.sysprocesses

  where spid = @@SPID

  and (program_name like 'Microsoft SQL Server Management Studio%' or

  program_name = 'SQL Query Analyzer'

  or program_name = 'Toad for Data Analysts'

  )) > 0

  BEGIN

  ROLLBACK;

  END

  END;

  But it isn't working as expected. I can still access via SSMS and TOAD.

  Any idea what is wrong with my trigger?

  Thanks!

• lbrady

  SSC Eights!

  Points: 883

  More actions

  October 28, 2013 at 2:18 pm

  #1662161

  Nevermind. I apologize for wasting people's time. If Mods see this, please delete.

  I didn't search the forum thoroughly enough. Found my answer