SQL SERVER 利用服务器级别触发器限制删库删表同时记录

创建记录删除操作的表

首先，我们需要创建一个表来记录尝试删除数据库和表的操作。该表将包括操作的详细信息，例如执行操作的用户、操作的时间、操作类型以及被操作的数据库和对象等。

USE master; -- 或者选择其他适合的数据库
GO

CREATE TABLE DeleteLogs (
    LogID INT PRIMARY KEY IDENTITY(1,1),
    DeletedBy NVARCHAR(50),
    DeleteDate DATETIME,
    DeleteQuery NVARCHAR(MAX),
    ActionType NVARCHAR(50),
    TargetDatabase NVARCHAR(128),
    TargetObject NVARCHAR(128)
);

创建触发器以阻止删除操作

接下来，我们将创建一个服务器级触发器来阻止任何尝试删除数据库或表的操作，并将这些操作的详细信息记录到上面创建的表中。

USE master;
GO

CREATE TRIGGER PreventDrop
ON ALL SERVER 
FOR DROP_DATABASE, DROP_TABLE
AS
BEGIN
    DECLARE @actionType NVARCHAR(50)
    DECLARE @targetDatabase NVARCHAR(128)
    DECLARE @targetObject NVARCHAR(128)
    DECLARE @deleteQuery NVARCHAR(MAX)

    SELECT 
        @actionType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(50)'),
        @targetDatabase = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'),
        @targetObject = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),
        @deleteQuery = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

    -- 取消操作
    ROLLBACK;

    -- 记录操作
    INSERT INTO master.dbo.DeleteLogs (DeletedBy, DeleteDate, DeleteQuery, ActionType, TargetDatabase, TargetObject)
    VALUES (SUSER_SNAME(), GETDATE(), @deleteQuery, @actionType, @targetDatabase, @targetObject);

    -- 提示用户
    PRINT '不允许删除所有库和表，此操作已被记录。';
END;

整理

以上两个步骤创建了一个用于记录尝试删除数据库和表的操作的系统。任何尝试删除数据库或表的操作都将被阻止，并且操作的详细信息将被记录到DeleteLogs表中。

通过查询DeleteLogs表，您可以查看尝试删除数据库或表的所有操作的历史记录，从而加强数据库的安全性和审计能力。

请注意，在实际生产环境中使用这些代码之前，请务必在测试环境中进行充分的测试。@TOC