failed logon trigger blocking access into SQL Server instance

Question

I wanted to create a trigger to record all the names of logins(who logon into system) in ServerLogonRecords table(all the columns are nullable) using LOGON trigger. I get information using eventdata() system function and convert the result into nvarchar type.

 create trigger tr_recorder
 on all server
 for logon
 as
 begin
 declare @var  nvarchar(70)
 set @var = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(70)')
 insert into ServerLogonRecords values(@var)
 end

Now I can't even login using my privileged account because all logins fail. I want to know why do logins fail (is my trigger doing something wrong?). And any suggestions how to fix this in order to be able to login again into the instance will be very appreciated.

Thanks.

Answer 1

And any suggestions how to fix this in order to be able to login again into the instance will be very appreciated.

You should disable your trigger:

disable trigger tr_recorder on all server;

You should be able to do it without problem if you log in as sysadmin because if your trigger is exactly as you posted, it will be executed without errors by sysadmin.

Or you should do it using DAC, i.e. you should connect to server using -A, in case your trigger is different and even sysadmin cannot login now.

Here you can find step to step instruction with screenshots: SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem.

I want to know why do logins fail (is my trigger doing something wrong?).

Your problem can be the following: your trigger tries to insert into a table in master database but usually users don't have any permission in master. Unless the login is sysadmin or is mapped to master explicitely, it is guest in master and has no permission on any user table in master.

---

To fix the issue you can use execute as clause in your logon trigger, this way the trigger will be executed with the permissions of the login that you put in execute as clause.

One osservation: if all you wanted to do is to record every successful login to server you can do it by changing login auditing:

This way you'll get all the successful logins in SQL Server errorlog.