2

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
0

"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

  • That's probably why it doesn't log the error but not why it would say "login failed due to trigger execution". – Joshua Barker Mar 26 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.