背景 為了監控好生產環境下各個資料庫伺服器上DDL操作日誌，便於運維工程師管控好風險，我們有必要關註當前實例下的所有的DDL操作。 測試環境 Microsoft SQL Server 2012 - 11.0.2218.0 (X64) Jun 12 2012 13:05:25 Copyright (c ...
背景
為了監控好生產環境下各個資料庫伺服器上DDL操作日誌，便於運維工程師管控好風險，我們有必要關註當前實例下的所有的DDL操作。測試環境
Microsoft SQL Server 2012 - 11.0.2218.0 (X64) Jun 12 2012 13:05:25 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )操作步驟
第一步.在監控庫中新建DDL監控表用來存放DDL監控日誌記錄
1 --新建監控庫，如果已存在該資料庫，可以不執行 2 USE master; 3 IF DB_ID('azure_monitor') IS NOT NULL 4 DROP DATABASE azure_monitor;
1 CREATE DATABASE azure_monitor 2 ON 3 --請根據實際情況選擇監控庫的存放路徑 4 ( NAME = azure_monitor, 5 FILENAME = 'd:\azure_monitor.mdf', 6 FILEGROWTH = 50MB 7 ) 8 LOG ON 9 ( NAME = azure_monitor_log, 10 FILENAME = 'd:\azure_monitore_log.ldf', 11 FILEGROWTH = 50MB 12 );
1 USE master; 2 ALTER DATABASE azure_monitor SET RECOVERY SIMPLE;
1 USE [azure_monitor]; 2 CREATE TABLE [dbo].[monitor_DatabaseLog] 3 ( 4 [DatabaseLogID] [INT] IDENTITY(1, 1) NOT NULL, 5 [PostTime] [DATETIME] NOT NULL, 6 [DatabaseUser] [sysname] NOT NULL, 7 [LoginName] [sysname] NOT NULL, 8 [Event] [sysname] NOT NULL, 9 [databasename] [sysname] NULL, 10 [Schema] [sysname] NULL, 11 [Object] [sysname] NULL, 12 [TSQL] [NVARCHAR](MAX) NOT NULL, 13 [XmlEvent] [XML] NOT NULL, 14 CONSTRAINT [PK_DatabaseLog_DatabaseLogID] 15 PRIMARY KEY NONCLUSTERED ([DatabaseLogID] ASC) 16 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 17 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 18 ALLOW_PAGE_LOCKS = ON 19 ) ON [PRIMARY] 20 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]; 21 GO
第二步.新建實例級別的觸發器
1 CREATE TRIGGER [ddlDatabaseTriggerLog] 2 ON ALL SERVER 3 WITH EXECUTE AS 'sa' ---根據實際情況選擇 4 FOR DDL_DATABASE_LEVEL_EVENTS, CREATE_DATABASE, DROP_DATABASE, ALTER_DATABASE, CREATE_LOGIN 5 AS 6 BEGIN 7 SET NOCOUNT ON; 8 DECLARE @data XML; 9 DECLARE @LoginName sysname; 10 DECLARE @databasename sysname; 11 DECLARE @schema sysname; 12 DECLARE @object sysname; 13 DECLARE @eventType sysname; 14 SET @data = EVENTDATA(); 15 SET @LoginName 16 = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'); 17 SET @databasename 18 = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname'); 19 SET @eventType 20 = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'); 21 SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'); 22 SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'); 23 IF @object IS NOT NULL 24 PRINT ' ' + @eventType + ' - ' + @databasename + '.' + @schema + '.' 25 + @object; 26 ELSE 27 PRINT ' ' + @eventType + ' - ' + @databasename + '.' + @schema; 28 IF @eventType IS NULL 29 PRINT CONVERT(NVARCHAR(MAX), @data); 30 ---寫入的日誌記錄對應的庫名是否正確 31 INSERT [azure_monitor].[dbo].[monitor_DatabaseLog] 32 ( [PostTime], 33 [DatabaseUser], 34 [LoginName], 35 [Event], 36 [databasename], 37 [Schema], 38 [Object], 39 [TSQL], 40 [XmlEvent] 41 ) 42 VALUES 43 ( GETDATE(), 44 CONVERT(sysname, CURRENT_USER), 45 CONVERT(sysname, @LoginName), 46 @eventType, 47 CONVERT(sysname, @databasename), 48 CONVERT(sysname, @schema), 49 CONVERT(sysname, @object), 50 @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 51 @data 52 ); 53 END; 54 GO
監控效果
後記
- 所有的人員登陸都已提前開設好各自的登陸用戶；
- 嚴格隔離區分不同的人員之間操作許可權；
參考
create trigger
