0

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
6
  • How many rows are returned by the query that populates @Logon? It seems that might return multiple rows... Commented May 12, 2020 at 15:45
  • Don't you get an error on that command SET @Logon =...? Commented May 12, 2020 at 15:46
  • @TonyHinkle For my test. Have only return 2 rows.
    – SakZepelin
    Commented May 12, 2020 at 15:56
  • @DenisRubashkin No. For example, when I enable the logon trigger and then I refresh the instance. Appears the problems
    – SakZepelin
    Commented May 12, 2020 at 15:57
  • Can you explain what you're trying to do with that? Assigning two rows to one varchar is going to fail--so logon trigger fails, causing logon to fail. Commented May 12, 2020 at 16:16

1 Answer 1

1

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.

1
  • I did some changes according to yours suggestions and also some changes with the insert command. Now looks better and also I tested with one colleague. Thank you very much.
    – SakZepelin
    Commented May 13, 2020 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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