sql - SQL Server 触发器无法回滚更改

Question

为了防止创建 \ 使用 CHECK_POLICY=OFF 更改 SQL 登录，我创建了一个服务器触发器：

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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 = 'XXX Policy: CHECK_POLICY=OFF is a forbidden setting. Rollback for a batch ' + @text 
        exec xp_logevent 60000, @MESSAGE_LOG , warning
        SET @MESSAGE_PRINT  = '
        ************************************************************
        ' + 'XXX Policy: 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 

如果我使用 t-sql 或 SSMS，它就可以正常工作，但如果使用 dbatools Commandlets 复制或创建登录名，它仍然可以创建登录名。这是触发代码问题和我的错误，还是 SQL powershell 模块使用数据库对象的非事务性创建？有什么方法可以捕获和回滚它？

谢谢！

标签： sqlsql-servertriggers