master Server Triggers

Overview

There are two kinds here and one of them is dangerous in a way nothing else in SQL Server is.

Kind What it does
DDL trigger at server scope Fires when somebody creates a login or alters a database, and can roll that statement back.
Logon trigger Fires on every connection, including the connection of the person trying to fix it.

A logon trigger that errors, blocks, or simply denies the wrong principal locks every user out of the instance including sysadmin.

The way back in is the dedicated admin connection, which is why this page says whether that is currently enabled rather than leaving it on another page.

The master Server Triggers page: the code that runs on every login or every DDL statement
The whole page. One DDL trigger at server scope and no logon trigger, which is close to the answer most instances give.

Where to find it

Expand a server in the tree, expand the master database, then Master → Server Triggers.

An empty page is good news

Most instances have none of these, so the page reads as an answer instead of as an absence.

A CLR trigger has no text

sys.server_sql_modules holds nothing for one.

The definition column degrades to the assembly and class it runs rather than drawing a blank cell that looks like a permission problem.

Reading the chart

The bars: logon triggers first, then DDL triggers by how many events they watch
A single DDL trigger watching two events. The bar is the event count, and a logon trigger would sort above it whatever it watched.

One bar per trigger. Logon triggers come first because those are the ones that can lock everybody out, then the DDL triggers by how many events they are registered for.

An instance with neither draws nothing here and says so in a line, which is the page reading as an answer rather than as an absence.

Reading the grid

The grid: kind, state, the events it fires on and the first line of its definition
One row per trigger, with the events it fires on and the start of its definition. Double-click a row for the whole thing.
Column What it is
Trigger The trigger name.
Kind DDL at server scope, or logon.
State Enabled or disabled. A disabled logon trigger is one somebody has already had trouble with.
Fires on The events it is registered for.
Created create_date.
Last changed modify_date.
First line The start of the definition, or the assembly and class for a CLR trigger.

Double-click a row for the whole definition.

The toolbar

Control What it does
Endpoints Opens master Endpoints, since a logon trigger fires on every connection through them.
Server permissions Opens master Server Permissions.
Change ledger Opens master Change Ledger, which is what a server DDL trigger is often written to do by hand.

Where the data comes from

Source What it gives
sys.server_triggers The triggers, their type, enabled state, create and modify dates.
sys.server_trigger_events Which events each one fires on.
sys.server_sql_modules The T-SQL definition, for the non-CLR ones.
sys.server_assembly_modules The assembly and class, for a CLR trigger.
sys.configurations Whether the dedicated admin connection accepts remote connections.
Report Why you would go there
master Endpoints The connections a logon trigger fires on.
master Server Permissions What a DDL trigger is watching for.
master Change Ledger The change record SQL Server already keeps.
master Startup Procedures The other code that runs where nobody is watching.
Configuration Values The dedicated admin connection setting in full.

Frequently asked questions

Nobody can log in and there is a logon trigger. Connect with the dedicated admin connection, which bypasses logon triggers, and disable it. This page says whether the DAC currently accepts remote connections, which decides whether you can do that from your desk or have to be on the server.

Can a logon trigger really lock out sysadmin? Yes. It fires on every connection including theirs. The DAC is the exception, and it is the reason the DAC exists.

Why does a trigger show no text? It is a CLR trigger. sys.server_sql_modules has no row for one, so the page shows the assembly and class instead of a blank cell.

Is an empty page a problem? No, it is the usual and the good answer. Most instances have no server triggers at all.