背景
为了监控好生产环境下各个数据库服务器上DDL操作日志，便于运维工程师管控好风险，我们有必要关注当前实例下的所有的DDL操作以及对应的IP和hostname。
测试环境
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 NULL4 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 = 50MB12 );
1 USE master;2 ALTER DATABASE azure_monitor SET RECOVERY SIMPLE;
1 USE [azure_monitor]; 2 3 4 CREATE TABLE [dbo].[monitor_DatabaseLog] 5 ( 6 [DatabaseLogID] [INT] IDENTITY(1, 1) NOT NULL, 7 [PostTime] [DATETIME] NOT NULL, 8 [DatabaseUser] [sysname] NOT NULL, 9 [LoginName] [sysname] NOT NULL,10 [Event] [sysname] NOT NULL,11 [databasename] [sysname] NULL,12 [Schema] [sysname] NULL,13 [Object] [sysname] NULL,14 [TSQL] [NVARCHAR](MAX) NOT NULL,15 [XmlEvent] [XML] NOT NULL,16 [IP] [NVARCHAR](32) NULL,17 [hostname] [NVARCHAR](100) NULL,18 CONSTRAINT [PK_DatabaseLog_DatabaseLogID]19 PRIMARY KEY NONCLUSTERED ([DatabaseLogID] ASC)20 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,21 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,22 ALLOW_PAGE_LOCKS = ON23 ) ON [PRIMARY]24 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];25 26 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 9 DECLARE @data XML;10 DECLARE @LoginName sysname;11 DECLARE @databasename sysname;12 DECLARE @schema sysname;13 DECLARE @object sysname;14 DECLARE @eventType sysname;15 DECLARE @ip VARCHAR(32) =16 (17 SELECT client_net_address18 FROM sys.dm_exec_connections19 WHERE session_id = @@SPID20 );21 22 DECLARE @hostname NVARCHAR(100) = HOST_NAME();23 24 SET @data = EVENTDATA();25 SET @LoginName26 = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');27 SET @databasename28 = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname');29 SET @eventType30 = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');31 SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');32 SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');33 34 IF @object IS NOT NULL35 PRINT ' ' + @eventType + ' - ' + @databasename + '.' + @schema + '.'36 + @object;37 ELSE38 PRINT ' ' + @eventType + ' - ' + @databasename + '.' + @schema;39 40 IF @eventType IS NULL41 PRINT CONVERT(NVARCHAR(MAX), @data);42 ---检查写入的日志记录对应的库名是否正确43 INSERT [azure_monitor].[dbo].[monitor_DatabaseLog]44 ( [PostTime],45 [DatabaseUser],46 [LoginName],47 [Event],48 [databasename],49 [Schema],50 [Object],51 [TSQL],52 [XmlEvent],53 [ip],54 [hostname]55 )56 VALUES57 ( GETDATE(),58 CONVERT(sysname, CURRENT_USER),59 CONVERT(sysname, @LoginName),60 @eventType,61 CONVERT(sysname, @databasename),62 CONVERT(sysname, @schema),63 CONVERT(sysname, @object),64 @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),65 @data,66 @ip,67 @hostname68 );69 END;
监控效果
后记
- 所有的人员登陆都已提前开设好各自的登陆用户；
- 严格隔离区分不同的人员之间操作权限；
---清空记录存储过程CREATE PROCEDURE [dbo].[usp_PurgeOldData_databaselog] ( @PurgeWaits SMALLINT )AS BEGIN; IF @PurgeWaits IS NULL BEGIN; RAISERROR(N'Input parameters cannot be NULL', 16, 1); RETURN; END; DELETE FROM [dbo].[monitor_DatabaseLog] WHERE [PostTime] < GETDATE() - @PurgeWaits; END;GO
参考