Sql-server – Logon Trigger Unexpected Result

sql servertrigger

I have only server logon trigger and it is being used to prevent logins coming from an application/server combination. It works appropriately for that application/server combination and gives the print statement errors in the error log.

However, I'm also getting sql errors for 2 other users that say "Severity 020 occurred, Logon failed for login 'randomuser' due to trigger execution." which don't come from that server/application combination and the received error message isn't what I've used in the trigger.

Any ideas what could be causing this?

I know that this isn't ideal and there are ways to bypass the checks but the use case is to prevent ssrs data sources coming from a dmz server being created that aren't using a predetermined restricted user account. The only ones that can create the data sources are internal employees with elevated permissions.

CREATE TRIGGER [LogonTrigger]
ON ALL SERVER
FOR LOGON 
AS
BEGIN
    BEGIN TRY

        DECLARE @host NVARCHAR(128) = ISNULL(HOST_NAME(), ''),
                @user NVARCHAR(128) = ISNULL(ORIGINAL_LOGIN(), ''),
                @app NVARCHAR(128) = ISNULL(APP_NAME(), '')


        IF (   @host = 'myhost'
               AND @user <> 'acceptableuser')
            BEGIN
                PRINT 'LogonTrigger Deny User:' + @user + ' Host:'
                      + @host + ' App: ' + @app
                ROLLBACK;
            END
    END TRY
    BEGIN CATCH
        PRINT 'LogonTrigger User:' + @user + ' ErrorNumber: '
              + CAST(ERROR_NUMBER() AS VARCHAR) + ' ErrorMessage: '
              + ERROR_MESSAGE()

    END CATCH
END

Best Answer

"Errors with a severity of 20 or higher that cause the Database Engine to terminate the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not terminated."

Could it be because of this?

Source

Related Question