Sql-server – Logon failed for login due to trigger execution / LOGON TRIGGER

logonsql serversql-server-2016ssmstrigger

I have this situation.
I created a logon script to block any session for specific logins. These logins are in a table.
So the behaviour of my script is simple.
Get the login from the table, compare it with the login who wants to access and then insert some information to another table.
The question here is:

I tested in one instance and everything went fine but in other instance and now in the previous instance when I enable the logon trigger appears a pop up and block my personal account.
My account is not included in the table so should not have this behaviour.
Obviously the part to insert date in a table, doesn't work.
Also other logins have been blocked

CREATE TRIGGER Accounts_V1
ON ALL SERVER WITH EXECUTE AS 'DBA'
 FOR LOGON
AS
BEGIN

DECLARE @Logon as varchar(50)
DECLARE @AppName as varchar(250)
DECLARE @ComputerName as varchar(50)
DECLARE @ServerName as varchar(50)
DECLARE @Original_Login as varchar(50)

SET @ComputerName =  HOST_NAME ()
SET @ServerName  =  @@servername 
SET @AppName =  APP_NAME()
SET @Original_Login = ORIGINAL_LOGIN ()
SET @Logon = ( select LoginName FROM [dba]..[MissusesAccounts_test]

where LoginName NOT like '%WIN%' and ( @AppName like '%Microsoft%' or @AppName like '%PowerShell%' ) and @ComputerName not like '%sql%')  


if (@Logon = @Original_Login)

    INSERT INTO [dba]..[LogonAccounts_test]
           ([ComputerName]
           ,[ServerName]
           ,[AppName]
           ,[Logon])
     VALUES
           (@ComputerName
           ,@ServerName
           ,@AppName
           ,@Logon)

        BEGIN
            print 'This login:  '+@Logon+' should not be used by individuals to run interactive queries in '
            +@AppName+' , and email has been sent to the Development Manager to investigate'

        END

END
REVERT

Best Answer

The trigger is failing because the query that is used to set @Logon is returning more than one row. Since @Logon is a varchar variable, an error occurs if anything more than one row is returned.

To test if the login is in the table, eliminate the @Logon parameter and use IF EXISTS to test if LoginName and the other criteria match a row in the table:

IF EXISTS (
    SELECT LoginName FROM [dba]..[MissusesAccounts_test]
    WHERE LoginName = @OriginalLogin
    AND LoginName NOT like '%WIN%' 
    AND ( @AppName LIKE '%Microsoft%' OR @AppName LIKE '%PowerShell%' ) 
    AND @ComputerName NOT LIKE '%sql%'
)

This is also safer from a logon trigger perspective because it always returns either true or false, so it will never cause an error (unless the MissusesAccounts table is dropped or modified).

You might also want to wrap this in some error handling so that if the MissusesAccounts or LogonAccounts_test table is accidentally dropped or modified, the error is handled, you are alerted, and it won't cause all logons to fail.

Related Question