创建登录名不被触发器识别，密码为空。

Question

因此，对于公司策略，我必须防止使用CHECK_POLICY=OFF设置创建SQL登录，因此我创建了一个触发器：

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

在CREATE LOGIN [test7] WITH PASSWORD=N'there is a password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF的情况下，触发器正在运行，并阻止使用CHECK_POLICY=OFF (脚本和图形用户界面)创建登录。

在密码为空的情况下:创建了CREATE LOGIN [test7] WITH PASSWORD=N'', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF登录，使用t或GUI并不重要。

您知道我应该如何修改触发器以防止使用CHECK_POLICY=OFF和空密码创建登录吗？

在SQL 2012、2016、2017和2019上测试

谢谢!

Answer 1

我修改了触发器，以便它打印EVENTDATA()的内容。对于带有密码的CREATE LOGIN，事件数据包含整个语句(在TSQLCommand/CommandText下)。如果有空密码，事件CommandText只包含--*CREATE LOGIN-----------------------------------------。由于触发器检查CommandText (在本例中只包含某种占位符)，因此它将不匹配。