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
- Test logon triggers in a non-production environment first. A trigger that rolls back logons can block all connections.
- Keep a rollback plan before you deploy a logon trigger.
- Do not create several logon triggers; keep only the ones that are required.
- The source wrote
sqlserver.exe; the executable issqlservr.exe. Its dash options were also written with typographic dashes, which were replaced by hyphens. - Check the sqlcmd options on your version with
sqlcmd -?before use, since the first line was copied from the source note as it is. - Stop the SQL Server service before running
sqlservr.exefrom a command prompt, and restart it normally afterwards.