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
Improve this question
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. 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 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

Reset to default
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.

Improve this answer
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. Commented May 13, 2020 at 11:03

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.