SQL Server – Delete SQL Logon TRIGGER syntax

To delete a SQL LOGON Trigger , use the  DROP TRIGGER  myTrigger ON ALL SERVER syntax.  It is a common mistake for DBAs to try  a  delete of SQL Logon Trigger with the DROP TRIGGER syntax , but without the ON ALL SERVER

A Logon Trigger executes a stored procedure in reaction to a LOGON event. The logon has authenticated but the logon has not yet  actually started a user session.

To view a list of LOGON triggers  , use the sys.server_triggers Catalog View.  This view contains all server level DDL triggers.    In contrast to the sys.triggers

Select * sys.server_triggers

Sometimes you may see the error message:

Msg 3701, Level 11, State 5, Server MYSERVER,  Line 1
Cannot drop the trigger 'MyTrigger', because it does not exist or you
do not have permission.

Check that you’ve added the ON ALL SERVER    appendix. Otherwise , you’re attempting to DROP a different type of trigger.

The full syntax to drop a SQL Server LOGON TRIGGER is :

DROP TRIGGER  myTrigger ON ALL SERVER

I’m including these Related Posts , as it is always possible to create a LOGON Trigger , which has logic, that blocks anyone logging on. In those circumstances , attempt a Logon from the Dedicated Administrator Connection  and Delete the Trigger

 Related Posts

SQL Sever - Dedicated Administrator Connection

Monitoring a Rollback and sys.dm_exec_requests

SQL Server – Restrict SQL Server Logons by IP with EVENTDATA and SQL Logon Trigger