failed logon trigger blocking access into SQL Server instance

1 vote

1 answer

1617 views

                          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)', '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.
                        

Asked by igelr (2162 rep)

Sep 6, 2018, 09:08 AM
Last activity: Sep 6, 2018, 09:57 AM

failed logon trigger blocking access into SQL Server instance

Related Questions