Sql-Server

如何擷取 sp_refreshsqlmodule 事件

  • October 21, 2015

我的問題：執行sp_refreshsqlmodule將更新sys.objects.modify_date值，但不會觸發 DDL 觸發器可以使用的 DDL 事件。因此，如果使用 DDL 觸發器來顯示對象更改歷史記錄，我無法協調在沒有針對對象執行modify_date任何語句時，過程、函式等將具有最近的原因。ALTER這可能會在審計期間引起一些麻煩。

我的問題：有什麼方法可以擷取sp_refreshsqlmodule事件，以便我可以自動協調modify_date數據庫對象？

我能夠找到 SQL Server 2008 的此連接項https://connect.microsoft.com/SQLServer/feedback/details/340441/sp-autostats-and-other-system-procedures-does-not-fire-ddl- triggers，使用註釋標記為已關閉：

我們認為為 SQL Server 2008 修復這個問題並不重要。我們肯定會在之後的版本中考慮它

我也可以在 SQL Server 2014 中複製該問題。

複製問題的 SQL 腳本：

IF OBJECT_ID('dbo.test_DDL_log ') IS NOT NULL
DROP TABLE dbo.test_DDL_log; 
GO

CREATE TABLE dbo.test_DDL_log (id int not null identity primary key, DDL_EventData xml, dateCreated datetime, contextInfo varchar(128));
go

CREATE TRIGGER [TEST_ddlDatabaseTriggerLog] 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
--Log all DDL operations on this database to an audit table.
BEGIN
   SET NOCOUNT ON;

   INSERT INTO dbo.test_DDL_log(DDL_EventData, dateCreated, contextInfo)
   VALUES (
         EVENTDATA()
       , GETDATE()
       , REPLACE(CAST(CONTEXT_INFO() AS VARCHAR(128)), CHAR(0), '')
   );
END
GO 

--Context info used in DDL trigger to tie to build and/or Support Ticket # to this change
DECLARE @c varbinary(128);
SET @c=cast('Ticket 123 v1.2.345' as varbinary(128));
SET CONTEXT_INFO @c;
GO

--Repro Example taken from:  https://msdn.microsoft.com/en-us/library/bb326754.aspx
-- Create an alias type.
IF EXISTS (SELECT 'TEST_mytype' FROM sys.types WHERE name = 'TEST_mytype')
DROP TYPE TEST_mytype;
GO

CREATE TYPE TEST_mytype FROM nvarchar(5);
GO

IF OBJECT_ID ('dbo.TEST_to_upper', 'FN') IS NOT NULL
DROP FUNCTION dbo.TEST_to_upper;
GO

CREATE FUNCTION dbo.TEST_to_upper (@a TEST_mytype)
RETURNS TEST_mytype
WITH ENCRYPTION
AS
BEGIN
RETURN upper(@a)
END;
GO

-- Increase the length of the alias type.
EXEC sp_rename 'TEST_mytype', 'TEST_myoldtype', 'userdatatype';
GO

CREATE TYPE TEST_mytype FROM nvarchar(10);
GO

---- The function parameter still uses the old type.
-- and would Fail here because of truncation:
--SELECT dbo.TEST_to_upper('abcdefgh'); 
GO

select modify_date_BEFORE = o.modify_date
from sys.objects o 
where o.name = 'TEST_to_upper'
;
go

WAITFOR DELAY '00:00:05'; --allow some time to elapse so that modify_date change is more noticable
GO

-- Refresh the function to bind to the renamed type.
EXEC sys.sp_refreshsqlmodule 'dbo.TEST_to_upper';
go

select modify_date_AFTER = o.modify_date
from sys.objects o 
where o.name = 'TEST_to_upper'
;
go

--only 4 events show here, NOT included the call to sp_refreshsqlmodule which updated the modify_date
SELECT * from dbo.test_DDL_log;
go

--CLEANUP 
IF OBJECT_ID ('dbo.TEST_to_upper', 'FN') IS NOT NULL
DROP FUNCTION dbo.TEST_to_upper;
GO
IF EXISTS (SELECT 'TEST_myoldtype' FROM sys.types WHERE name = 'TEST_myoldtype')
DROP TYPE TEST_myoldtype;
GO
IF EXISTS (SELECT 'TEST_mytype' FROM sys.types WHERE name = 'TEST_mytype')
DROP TYPE TEST_mytype;
GO
if exists(select 1 from sys.triggers t where t.name = 'TEST_ddlDatabaseTriggerLog')
DROP TRIGGER [TEST_ddlDatabaseTriggerLog] ON DATABASE
GO

IF OBJECT_ID('dbo.test_DDL_log ') IS NOT NULL
DROP TABLE dbo.test_DDL_log; 
GO

該腳本的結果顯示：

modify_date_BEFORE
-----------------------
2015-10-16 10:59:10.447

modify_date_AFTER
-----------------------
2015-10-16 10:59:15.487

id          DDL_EventData                                                                                         dateCreated             contextInfo
----------- ------------------------------------------------------------------------------------------------ -------------------------    ------------------------
1           <EVENT_INSTANCE><EventType>CREATE_TYPE</EventType><PostTime>2015-10-16T10:59:10.443</PostTime>....... 2015-10-16 10:59:10.443 Ticket 123 v1.2.345
2           <EVENT_INSTANCE><EventType>CREATE_FUNCTION</EventType><PostTime>2015-10-16T10:59:10.447</PostTime>... 2015-10-16 10:59:10.447 Ticket 123 v1.2.345
3           <EVENT_INSTANCE><EventType>RENAME</EventType><PostTime>2015-10-16T10:59:10.450</PostTime>............ 2015-10-16 10:59:10.450 Ticket 123 v1.2.345
4           <EVENT_INSTANCE><EventType>CREATE_TYPE</EventType><PostTime>2015-10-16T10:59:10.453</PostTime>....... 2015-10-16 10:59:10.453 Ticket 123 v1.2.345

的事件sp_refreshsqlmodule無處可尋，所有記錄的事件都在sp_refreshsqlmodule2015-10-16 10:59:15.487 之前（注意：我sp_refreshsqlmodule在上面的腳本中的呼叫前延遲了 5 秒以使其更明顯的是沒有記錄此事件）。

不幸的是，有幾個 DDL 類型的事件實際上並不能算作用於 DDL 觸發器、事件通知等的 DDL 事件。

sp_refreshsqlmodule您可以使用以下擴展事件會話擷取呼叫：

CREATE EVENT SESSION [refreshes] ON SERVER 
ADD EVENT sqlserver.module_end
(
 SET collect_statement = (1)
 ACTION
 (
    sqlserver.client_app_name, 
    sqlserver.client_hostname,
    sqlserver.database_id,
    sqlserver.database_name,
    sqlserver.username,
    sqlserver.context_info
 )
 WHERE ([object_id] = -419385653) -- OBJECT_ID(N'sys.sp_refreshsqlmodule')
)
ADD TARGET package0.asynchronous_file_target
(
 SET FILENAME = N'C:\temp\refreshes.xel'
);
GO

ALTER EVENT SESSION [refreshes] ON SERVER STATE = START;
GO

您可能需要收集一組不同的審計列；這些主要是從我的類似會議中藉來的。您可能還想為sp_refreshview重新編譯和統計更新等其他事件添加一個額外的過濾器（我不知道所有可能更改modify_date但未被擷取為正確 DDL 事件的過程呼叫）。

現在，會話將僅擷取數據。您可以像這樣手動檢查它：

;WITH ee_data AS 
(
 SELECT x = CONVERT(XML, event_data)
   FROM sys.fn_xe_file_target_read_file
   (N'C:\temp\refreshes*.xel', NULL, NULL, NULL)
)
SELECT 
 [statement] = x.value('(event/data[@name="statement"]/value)[1]','nvarchar(4000)'),
 [timestamp] = x.value('(event/@timestamp)[1]','datetime2'),
 --username = x.value('(event/action[@name="username"]/value)[1]','nvarchar(400)'),
 --[host] = x.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(400)'),
 --app = x.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(400)')
 -- you'll have to add the xquery stuff to get context_info
FROM ee_data;

範例輸出：

statement                                      timestamp
--------------------------------------------   ---------------------------
EXEC sys.sp_refreshsqlmodule N'dbo.someview'   2015-10-16 16:45:35.6220000

您必須解析出對象名稱，並將資訊放入您自己的 XML 格式以匹配EVENTDATA(). 另請注意，這timestamp是 UTC，而不是您的本地時間，因此您需要調整它以進行有效比較modify_date（繼承伺服器時間）。modify_date在我的情況下，和之間的差異timestamp是 6 毫秒，因此會話不會記錄對像被修改的確切時刻——您需要留出一點餘地來“匹配”這兩個值。

然後，您需要將最終得到的任何程式碼放入某種作業中，以輪詢文件目標以查找新行（您可能會考慮為此進行查詢通知以避免輪詢，但輪詢要簡單得多），並將它們插入到您的DDL 審計表。

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

相關問答

Sql-Server

動態創建觸發器以審核行更改

  • February 11, 2017
檢視問答
Sql-Server

確定 DDL 觸發器中的命令類型/獲取新列的名稱和數據類型

  • December 28, 2016
檢視問答
Sql-Server

伺服器範圍的 DDL 觸發器權限問題

  • February 2, 2014
檢視問答
Sql-Server

在 DELETE 上使用觸發器來檢索原始 SQL 命令和執行此操作的使用者

  • September 29, 2021
檢視問答
Sql-Server

觸發器以在創建時更改數據庫排序規則

  • May 13, 2020
檢視問答
Sql-Server

將有關誰刪除記錄的資訊傳遞到刪除觸發器

  • September 11, 2018
檢視問答