Most RecentMost ViewedMost LikesSo you have a slow logon…? (Part 2) First published on TechNet on Sep 24, 2009 Bob Drake again and welcome back for the second part of the slow logon series. 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' begin return; end; if ORIGINAL_LOGIN() = 'OMEGACAEVDEV1' begin -- not allowed to logon rollback; end; -- Insert Trail EXEC [OmegaCoreAudit].[OMEGACA].[P_ACC_UNF_TRAIL] 0, 'trail_details', 'ip', 'server', 'db', 0 ; END GO It does (as expected): OMEGACAEVDEV1 is not allowed to logon OMEGACAEVDEV2 is allowed to logon An audit event is inserted by proc P_ACC_UNF_TRAIL in a table for both users. All three above I want to stay this way ! But I need to have error handling in it, so that in case of whatever error the login is allowed to login - but keeping a record on another table named SYS_ERROR_LOG (with error details). In this trigger I have intentionally provoked an error by "select 10/0" The new trigger is: ALTER TRIGGER [LOG_TRG_02] ON ALL SERVER WITH EXECUTE AS 'SA' FOR LOGON AS BEGIN BEGIN TRY if ORIGINAL_LOGIN() = 'sa' begin return; end; --provoke error select 10/0; if ORIGINAL_LOGIN() = 'OMEGACAEVDEV1' begin -- not allowed to logon rollback; end; -- Insert Trail EXEC [OmegaCoreAudit].[OMEGACA].[P_ACC_UNF_TRAIL] 0, 'trail_details', 'ip', 'server', 'db', 0 ; END TRY BEGIN CATCH Insert into OmegaCoreAudit.OMEGACA.SYS_ERROR_LOG ([LOGIN_USER_NAME], [DB_USER_NAME], [USERHOST], [IP_ADDRESS], [OS_USER], [BG_JOB_ID], [ERROR_DATA]) values ('LOGIN_NAME', 'USER_NAME', 'USER_HOST', 'IP', NULL, NULL, 'ERROR_MESSAGE'); END CATCH END GO In the above code "if ORIGINAL_LOGIN() = 'OMEGACAEVDEV1'" represents a simplified version of a wider authorization process. Problem: CATCH is not working. Both users are not allowed to logon ("Logon failed for login '[user]' due to trigger execution") No record is written on table SYS_ERROR_LOG. I was expecting one for each user. What can I do to fix this problem? best regards Altin SolvedUsing Debugging Tools to Find Token and Session LeaksFirst published on TechNet on Apr 05, 2017 Hello AskDS readers and Identity aficionados.Monthly Mail Sack: Yes, I Finally Admit It Edition First published on TechNet on Aug 24, 2012 Heya folks, Ned here again. New Slow Logon, Slow Boot Troubleshooting ContentFirst published on TechNet on May 01, 2012 Hi all, Ned here again.Friday Mail Sack: It’s a Dog’s Life EditionFirst published on TechNet on Jan 20, 2012 Hi folks, Ned here again with some possibly interesting, occasionally entertaining, and always unsolicited Friday mail sack.Friday Mail Sack: Peevish Nediquette EditionFirst published on TechNet on Jul 15, 2011 Hi folks, Ned here again.Friday Mail Sack: LeBron is not Jordan EditionFirst published on TechNet on Jun 10, 2011 Hi folks, Ned here again.Friday Mail Sack: Tuesday To You EditionFirst published on TechNet on May 27, 2011 Hi folks, Ned here again.Friday Mail Sack: Now with 100% more wordsFirst published on TechNet on Apr 15, 2011 Hi folks, Ned here again.
