LLI Wiki — My Digital Knowledge Base

Recover from a blocking logon trigger

List server-level triggers, connect through the DAC, and disable or remove a logon trigger that blocks access.

A logon trigger that rolls back connections can block every login, administrators included. This page lists the server-level triggers, shows how to reach the instance through the dedicated administrator connection (DAC) to fix it, and how to disable or remove the trigger. The original article is an overview of logon triggers on SQLShack.

List the server-level triggers

SELECT * FROM sys.server_triggers;

Connect through the dedicated administrator connection

sqlcmd -A -D master
sqlcmd -S localhost -U sa -P '<PASSWORD>' -d master -A

In SQL Server Management Studio, the DAC is reached with the admin: prefix in the server name: admin:<instance_name>.

Disable the triggers

DISABLE TRIGGER ALL ON ALL SERVER;
DISABLE TRIGGER logonTrigger ON ALL SERVER;
DISABLE TRIGGER [tr_LogOn_CheckIP] ON ALL SERVER;

Disabling a trigger keeps its definition, so it can be enabled again later. Use DROP TRIGGER only when the trigger must be removed for good:

DROP TRIGGER triggername ON ALL SERVER;

Start SQL Server in minimal mode when the DAC is unavailable

sqlservr.exe -c -m -f
Option Effect
-c Starts SQL Server without waiting for the Service Control Manager
-m Starts in single-user mode
-f Starts with minimal configuration

Notes

Mastodon LLIMastodon SocialFosstodon