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
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
But it isn't working as expected. I can still access via SSMS and TOAD.
Any idea what is wrong with my trigger?