DDL觸發權限錯誤

我有兩個使用者，user1並且user2.

User1有系統管理員權限，我通過 sa 創建了一個數據庫級觸發器。

現在我正在嘗試通過user2who has db-ddladminrights 和db_datareader.

我收到錯誤：

'Msg 297, Level 16, State 1, Procedure TR_CaptureDBChanges, Line 35
The user does not have permission to perform this action.

程式碼：

CREATE  TRIGGER [TR_CaptureDBChanges]
ON DATABASE
FOR  CREATE_PROCEDURE, ALTER_PROCEDURE,DROP_PROCEDURE,CREATE_TABLE, ALTER_TABLE,DROP_TABLE,CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,CREATE_VIEW,ALTER_VIEW,DROP_VIEW,CREATE_FUNCTION, ALTER_FUNCTION,
        DROP_FUNCTION
BEGIN 
DECLARE @ed XML 
SET @ed = EVENTDATA() 
DECLARE    @ip VARCHAR(32) =   (SELECT client_net_address FROM sys.dm_exec_connections  WHERE session_id = @@SPID);

   INSERT INTO DBChangesLogs ( EventDate,DBName)VALUES(GetDate(),@ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'))
END

最有可能的問題是testuser沒有VIEW SERVER STATE伺服器級別的權限。訪問sys.dm_exec_connections DMV需要該權限（分配給數據庫中的登錄[master]，而不是分配給具有 DDL 觸發器的數據庫中的使用者） 。因此，您需要執行以下操作之一：

1. GRANT``testuser登錄VIEW SERVER STATE伺服器級權限，或
2. 創建一個基於證書的登錄，然後為其分配VIEW SERVER STATE伺服器級權限，然後使用 ADD SIGNATURE 使用該證書對 DDL 觸發器進行簽名。以下答案中提供了此步驟：

我需要向使用者提供哪些最低權限才能檢查 SQL Server 代理服務的狀態？

從安全的角度來看，我建議做一些額外的工作來處理基於證書的登錄和簽署觸發器。此方法不授予任何實際登錄/使用者任何額外權限，並將應用於任何具有適當 DDL 權限的使用者，以執行會導致 DDL 觸發器觸發的操作。此外，假設這testuser僅僅是一個測試案例，並且這些權限需要在最終系統中更廣泛地應用，那麼您可能不想授予VIEW SERVER STATE所有這些使用者甚至角色。

按照同樣的構想，我認為所有權連結會處理INSERT權限，DBChangesLogs這樣就不必將權限顯式授予任何使用者。如果不是這種情況，那麼與其將這個權限授予一個或多個使用者——或者肯定比將任何使用者添加到db_datawriter授予對所有表的訪問權限（並且不僅僅是INSERT）的角色更好——你可以簡單地做兩個額外的，除了其他答案中已經概述的步驟之外，授予此觸發器插入DBChangesLogs表的能力的簡單步驟：

1. （從上面連結的“最低權限…”答案中概述的步驟開始，最後一部分是在存在 DDL 觸發器的數據庫中創建證書）
2. 從先前步驟中創建的基於證書的登錄創建使用者（或從現在也在此數據庫中的同一個證書創建使用者 - 在任何一種情況下創建的使用者都是相同的）
3. GRANT此使用者對錶的INSERT權限DBChangesLogs

CREATE USER [MrDoStuff] FROM LOGIN [MrDoStuff];
GRANT INSERT ON dbo.DBChangesLogs TO [MrDoStuff];

PS：您聲明並設置@ip變數。我假設您將在此權限問題解決後使用它？

您的 SELECT 語句sys.dm_exec_connections需要VIEW SERVER STATE權限。

因此，要授予權限，您可以執行：

USE MASTER
GO
GRANT VIEW SERVER STATE TO user2;
GO

接下來是INSERT觸發器中的語句。登錄user2需要db_datawriter對您創建表的位置的權限DATABASE才能寫入DBChangesLogs表。

您可以使用sp_addrolemember授予讀寫權限：

USE [DATABASE_NAME]
GO
EXEC sp_addrolemember N'db_datareader', N'user2'
GO
USE [DATABASE_NAME]
GO
EXEC sp_addrolemember N'db_datawriter', N'user2'
GO

最後一件事，您用於創建觸發器的程式碼在 BEGIN 之前缺少一個“AS”。

引用自：https://dba.stackexchange.com/questions/120485