问用于用户审核的系统视图触发器的替代方法
EN

Database Administration用户

提问于 2017-05-16 06:23:24

回答 2查看 629关注 0票数 5

即使是写标题，我也对我尝试过的想法感到失望(但是，在这个过程中，我学到了一些东西)。

对于在数据库上运行迁移脚本的开发人员，以及在覆盖数据库中已经存在的存储过程(DEV，谢天谢地)过程中，我遇到了问题。

但是，没有办法找出是谁做了这些更改，因为：

他们不承认
Server不填充principal_id系统视图中的sys.objects列，因为每个人都以dbo (这里有更多的信息)的形式连接。

因此，我尝试在sys.objects上编写一个触发器，但这没有起作用(我不知道为什么我曾经期望它)，因为我收到了一个错误：

对象'DEV_DB.sys.objects‘不存在或对此操作无效。

我还尝试为每个开发人员创建一个用户，以便当他们进行自动迁移时，他们将使用我所做的用户，希望该开发人员对sys.objects所做的所有更改都将作为他们登录的用户登录。

但这也不起作用，因为principal_id还没有填充(我所做的是为每个开发人员创建一个新的登录/用户& DB，并给出登录特定服务器角色：public__、db_datawriter__、db_datareader__、db_ddladmin)。

关于我还能尝试什么替代方案，有什么建议吗？

sql-server

sql-server-2008-r2

audit

AIGC及大模型加速场景解决方案

为有AI内容生成、大模型需求的企业客户快速打造一站式方案

回答 2

Database Administration用户

回答已采纳

发布于 2017-05-16 06:41:44

您始终可以点击默认跟踪来捕获对象的任何更改，包括存储的procs。

给下面的脚本一个旋转，这是很容易找出谁改变了什么和什么时候。它帮助我抓住了不止一个狡猾的开发人员;)

SELECT  t.DatabaseName, t.ObjectID,t.NTUserName,t.HostName,t.StartTime, te.name
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL 
Server\MSSQL13.SQL2016\MSSQL\Log\log.trc', DEFAULT) t  
JOIN sys.trace_events te  on t.EventClass = te.trace_event_id  
ORDER BY t.StartTime DESC;

您需要将路径更改到SQL跟踪文件所在的位置，以找出它的运行位置：

select path 
from sys.traces
where id = 1

提示之一，从文件名中删除_number以读取所有可用的跟踪文件，例如，如果文件名为log_16.trc，则将其更改为log.trc。

票数 5

Database Administration用户

发布于 2017-05-16 09:44:24

如果开发人员有单独的登录，您可以考虑使用DDL触发器。下面是CREATE_PROCEDURE、ALTER_PROCEDURE和DROP_PROCEDURE事件的示例。

USE YourDatabase;
GO

CREATE TRIGGER trgStoredProcDDL
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    --Get relevant info from EVENTDATA()
    DECLARE @Login SYSNAME= EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME');
    DECLARE @DBUser SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME');
    DECLARE @Schema SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME');
    DECLARE @Proc SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME');

    --Optional: error message for end user.
    RAISERROR ('Please stop!', 16, 1);

    --Optional: Rollback transaction that fired the DDL trigger
    ROLLBACK;

    --Optional: log some data to a table.
    BEGIN TRAN
        INSERT INTO guest.LogTable(LogMsg, LogDate, [Login], [User])
        VALUES('Stored Proc was created/altered/dropped: [' + @Schema + '].[' + @Proc+ ']', 
            CURRENT_TIMESTAMP,
            @Login,
            @DBUser);
    COMMIT

    --Optional: send an email/alert.
    DECLARE @Subj NVARCHAR(255) 
    SET @Subj = @@SERVERNAME + ' - Stored Proc created/altered/dropped'
    EXEC msdb..sp_send_dbmail
        @recipients = 'You@YourDomain.com', 
        @subject = @Subj,
        @body = 'Your message...'
END;