Removing a DDL Trigger

In a recent post I talked about how to create a DDL trigger. You’d think to drop that trigger, I’d run this:

DROP trigger CatchLogins

That returns me this nice message:

Msg 3701, Level 11, State 5, Line 1

Cannot drop the trigger ‘CatchLogins’, because it does not exist or you do not have permission.

I was logged in as a sysadmin, and I’d created the trigger in the same session, so it doesn’t make sense.

Instead you need to add a little phrase:

DROP trigger CatchLogins
 ON ALL SERVER
;

Then you get the wonderful

Command(s) completed successfully.