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