背景
为了监控好生产环境下各个数据库服务器上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监控日志记录
--新建监控库，如果已存在该数据库，可以不执行
USE master;
IF DB_ID('azure_monitor') IS NOT NULL
DROP DATABASE azure_monitor;
CREATE DATABASE azure_monitor
ON
--请根据实际情况选择监控库的存放路径
( NAME = azure_monitor,
FILENAME = 'd:\azure_monitor.mdf',
FILEGROWTH = 50MB
)
LOG ON
( NAME = azure_monitor_log,
FILENAME = 'd:\azure_monitore_log.ldf',
FILEGROWTH = 50MB
);
USE master;
ALTER DATABASE azure_monitor SET RECOVERY SIMPLE;
USE [azure_monitor]; CREATE TABLE [dbo].[monitor_DatabaseLog]
(
[DatabaseLogID] [INT] IDENTITY(1, 1) NOT NULL,
[PostTime] [DATETIME] NOT NULL,
[DatabaseUser] [sysname] NOT NULL,
[LoginName] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[databasename] [sysname] NULL,
[Schema] [sysname] NULL,
[Object] [sysname] NULL,
[TSQL] [NVARCHAR](MAX) NOT NULL,
[XmlEvent] [XML] NOT NULL,
[IP] [NVARCHAR](32) NULL,
[hostname] [NVARCHAR](100) NULL,
CONSTRAINT [PK_DatabaseLog_DatabaseLogID]
PRIMARY KEY NONCLUSTERED ([DatabaseLogID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]; GO
第二步.新建实例级别的触发器
CREATE TRIGGER [ddlDatabaseTriggerLog]
ON ALL SERVER
WITH EXECUTE AS 'sa' --根据实际情况选择
FOR DDL_DATABASE_LEVEL_EVENTS, CREATE_DATABASE, DROP_DATABASE, ALTER_DATABASE, CREATE_LOGIN
AS
BEGIN
SET NOCOUNT ON; DECLARE @data XML;
DECLARE @LoginName sysname;
DECLARE @databasename sysname;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
DECLARE @ip VARCHAR(32) =
(
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
); DECLARE @hostname NVARCHAR(100) = HOST_NAME(); SET @data = EVENTDATA();
SET @LoginName
= @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
SET @databasename
= @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname');
SET @eventType
= @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'); IF @object IS NOT NULL
PRINT ' ' + @eventType + ' - ' + @databasename + '.' + @schema + '.'
+ @object;
ELSE
PRINT ' ' + @eventType + ' - ' + @databasename + '.' + @schema; IF @eventType IS NULL
PRINT CONVERT(NVARCHAR(MAX), @data);
---检查写入的日志记录对应的库名是否正确
INSERT [azure_monitor].[dbo].[monitor_DatabaseLog]
( [PostTime],
[DatabaseUser],
[LoginName],
[Event],
[databasename],
[Schema],
[Object],
[TSQL],
[XmlEvent],
[ip],
[hostname]
)
VALUES
( GETDATE(),
CONVERT(sysname, CURRENT_USER),
CONVERT(sysname, @LoginName),
@eventType,
CONVERT(sysname, @databasename),
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
@data,
@ip,
@hostname
);
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