Well this was an odd one, and luckily on my test environment but definitely one I’ll look out for in the future. I was doing some maintenance removing old unused test databases when suddenly I got hit with this error:
Logon failed for login ‘ROBLT\RobSQL’ due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)
This was not a fun error. Everything I tried to do in the object explorer generated this error. Basically I couldn’t create any new connections to the database, I did have an existing query window open to master database which was still working which turned out to be my saving grace, as you’ll see later.
The error is pretty clear about what is going on. There is a trigger that is failing which is preventing any new logins from being established. If this was a production server it would mean NO-ONE could connect at all, which would be raise the stakes somewhat from my play server. The thing is, as a test server I had no idea what trigger it was talking about. I vaguely remember playing with login triggers 6 months ago but have no idea which trigger I needed to stop. So from my open query window to master, I stopped them all with the below TSQL:
DISABLE TRIGGER ALL ON ALL SERVER
This immediately allowed me to access the object explorer and go and look for the specific trigger that had caused the problem:
This trigger had of course been trying to write into one of the databases I had cleaned up. Lesson learnt – check for server scoped triggers before you hit that delete button.
There’s a number of other scenario’s that could get you into this pickle – such as creating a trigger to whitelist logins and forgetting to add yourself, or creating a trigger that references an object that doesn’t exist. If you want to be more surgical you can check for the trigger name with:
select * from sys.server_triggers
All this of course assumes you can get into the server in the first place. I was lucky enough to have an open live connection. If you don’t a failing trigger will prevent EVERYONE from connecting to the server, including sysadmins. You don’t have to hand in your resignation just yet though. Microsoft has foreseen that people will do stupid things with login triggers so there’s a few ways to address it outlined in the documentation:
- Login using the dedicated Administrator Connection: Details of how to do that are here if you are not sure. The DAC is not affected by login triggers. If you have enabled the setting “Remote Admin Connections”(And you should) you can do this remotely, otherwise you need to be on the same server that SQL is installed on.
- Start SQL in minimal configuration mode. You can do this by adding the -f flag when you start the server.
Either of these options will allow you to log into the server and disable or fix the trigger.