测试2

背景

为了监控好生产环境下各个数据库服务器上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 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 
 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 = ON
23              ) 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_address
18                 FROM sys.dm_exec_connections
19                 WHERE session_id = @@SPID
20             );
21 
22     DECLARE @hostname NVARCHAR(100) = HOST_NAME();
23 
24     SET @data = EVENTDATA();
25     SET @LoginName
26         = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
27     SET @databasename
28         = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname');
29     SET @eventType
30         = @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 NULL
35         PRINT '  ' + @eventType + ' - ' + @databasename + '.' + @schema + '.'
36               + @object;
37     ELSE
38         PRINT '  ' + @eventType + ' - ' + @databasename + '.' + @schema;
39 
40     IF @eventType IS NULL
41         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     VALUES
57     (   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         @hostname
68     );
69 END;

 

监控效果

监控效果

后记

  1. 所有的人员登陆都已提前开设好各自的登陆用户；
  2. 严格隔离区分不同的人员之间操作权限；
 

参考

 

内容来源于网络如有侵权请私信删除
标签： SQL Server数据库 SQL Server技术文章
你还没有登录，请先登录或注册！
  • 还没有人评论，欢迎说说您的想法！