Forum Discussion
Solved
Catch Error in a SQL Server Logon Trigger
I have written a Logon trigger in a SQL Server to control logon authorization. ALTER TRIGGER [LOG_TRG_01]
ON ALL SERVER WITH EXECUTE AS 'SA'
FOR LOGON
AS
BEGIN
if ORIGINAL_LOGIN() = 'sa'
begi...
Yes, base on my test, I think it's correct.
I don't think there has a way to allow login when an exception raised in the trigger.
Exceptions which severity level greater than 10 can be catched by CATCH block, but such level exception in the trigger will cause the status of the implicit transaction turns to XACT_STATE() = -1, it means this transaction can only be rollback, the login has to be refused.
You have to find another way to log errors independently of transactions, such as SQL mail, write to a file...
Thank You very much rodgerkong for these two detailed answers
Yes - the XACT_STATE() = -1
Still looking on the first answer ... will reply soon.