CREATE LOGIN is not recognised by a trigger it password is empty



  • Accordingly to company policy I have to prevent creating SQL logins with CHECK_POLICY=OFF setting, so I've created a trigger:

    CREATE TRIGGER [create_or_alter_login] ON ALL SERVER
    FOR CREATE_LOGIN, ALTER_LOGIN
    as
    begin
        DECLARE @data xml
        DECLARE @text nvarchar(max);
        DECLARE @user nvarchar(max);
        DECLARE @object_name nvarchar(max);
        DECLARE @MESSAGE_LOG varchar(255);
        DECLARE @MESSAGE_PRINT varchar(255);
    
    SET @data = EVENTDATA();
    SET @text = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)');
    SET @user = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)');
    SET @object_name = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)');
    IF(@text LIKE N'%CHECK_POLICY=OFF%')
    BEGIN
        SET @MESSAGE_LOG = 'CHECK_POLICY=OFF is a forbidden setting. Rollback for a batch ' + @text 
        exec xp_logevent 60000, @MESSAGE_LOG , warning
        SET @MESSAGE_PRINT  = '
        ************************************************************
        ' + 'CHECK_POLICY=OFF is a forbidden setting.
        ' + 'Rollback SQL login change for '+ @object_name + '
        ************************************************************
        '
        Rollback
        RAISERROR (@MESSAGE_PRINT,10,1)
        
    END
    ELSE
    BEGIN
        SET @MESSAGE_LOG = 'SQL Login has been created or changed: ' + @text
        exec xp_logevent 60000, @MESSAGE_LOG , informational
    END
    

    END

    In case of CREATE LOGIN [test7] WITH PASSWORD=N'there is a password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF trigger is functioning and blocks creating a login with CHECK_POLICY=OFF (script and GUI)

    In case of empty password: CREATE LOGIN [test7] WITH PASSWORD=N'', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF login is created and it doesn't matter if I use t-sql or GUI.

    Any idea what happens where and how I should modify a trigger to prevent creating a login with CHECK_POLICY=OFF and empty password?

    Tested on SQL 2012, 2016, 2017 and 2019

    Thank you!



  • I altered the trigger so that it prints the content of EVENTDATA(). In case of a CREATE LOGIN with a password, the event data contains the whole statement (under TSQLCommand/CommandText). In case there is an empty password, the event CommandText only contains --*CREATE LOGIN-----------------------------------------. Since the trigger checks the CommandText, which in this case contains only some kind of placeholder, it would not match.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2