MS SQL监控数据库的DDL操作

GPS平台、网站建设、软件开发、系统运维，找森大网络科技！
https://cnsendnet.taobao.com
来自森大科技官方博客
http://www.cnsendblog.com/index.php/?p=1778

 

前言： 有时候，一个数据库有多个帐号，包括数据库管理员，开发人员，运维支撑人员等，可能有很多帐号都有比较大的权限，例如DDL操作权限(创建，修改，删除存储过程，创建，修改，删除表等），账户多了，管理起来就会相当麻烦，容易产生混乱，如果数据库管理员不监控数据库架构变更的话，就不知道谁对数据库架构做了啥改动（此处改动仅仅只DDL操作），尤其有时候，有些开发人员可能不按规章制度办事，绕过或忘了通知发布人员或DBA，直接去生产机做一些DDL操作，那么我们就需要对数据库架构某些更改的事件进行监控，如果能够监控并留下证据，这样既可以让DBA或相关管理人员知晓这些变更，有效管理数据库，也可以避免出现问题，出现扯皮现象，最后DBA成了背黑锅的。

下面就是一个解决上述问题的方案，我们通过创建一个表DatabaseLog和DDL触发器来解决问题，首先在msdb数据库里面新建一个表DatabaseLog，用来保存DDL触发器获取的信息。其中DDL触发器主要通过EVENTDATA()函数返回有关服务器或数据库事件的信息。

SQL Code 1

1. USE msdb;
2.  
3. GO
4.  
5.  
6.  
7. CREATE TABLE [dbo].[DatabaseLog]
8.  
9. (

11.[DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,

13.[PostTime] [datetime] NOT NULL,

15.[DatabaseUser] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

17.[LoginName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

19.[ClientHost] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

21.[Event] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

23.[Schema] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

25.[Object] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

27.[TSQL] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

29.[XmlEvent] [xml] NOT NULL,

31.CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED

33.(

35.[DatabaseLogID] ASC

37.)WITH (PAD_INDEX= OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

39.) ON [PRIMARY]

43.GO

45.EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'Primary key for DatabaseLog records.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'DatabaseLogID'

47.GO

49.EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The date and time the DDL change occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'PostTime'

51.GO

53.EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The user who implemented the DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'DatabaseUser'

55.GO

57.EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The login which implemented the DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'LoginName'

59.GO

61.EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The client machine on which implemented the DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'ClientHost'

63.GO

65.EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The type of DDL statement that was executed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Event'

67.GO

69.EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The schema to which the changed object belongs.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Schema'

71.GO

73.EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The object that was changed by the DDL statment.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Object'

75.GO

77.EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The exact Transact-SQL statement that was executed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'TSQL'

79.GO

81.EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The raw XML data generated by database trigger.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'XmlEvent'

83.GO

85.EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'Audit table tracking all DDL changes made to the database. Data is captured by the database trigger ddlDatabaseTriggerLog.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog'

87.GO

89.EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'Primary key (nonclustered) constraint' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'CONSTRAINT',@level2name=N'PK_DatabaseLog_DatabaseLogID'

93.GO

例如，我要监控数据库MyAssistant的DDL操作，那么我们首先在“数据库邮件”里面创建一个配置名为“ DataBase_DDL_Event”的配置文件（profile name）,这个就不多讲了，不知道配置的，自己先练练手把，假如我需要让数据库把监控到DDL操作变动相信信息发送到我的邮箱 *****@***.com(用你自己的邮箱替代)，那么只需要修改下面代码的邮箱和profile_name即可。

SQL Code 2

1. USE MyAssistant;
2. GO
3.  
4. CREATE TRIGGER [DTG_DatabaseDdlTriggerLog]
5. ON DATABASE
6. FOR DDL_DATABASE_LEVEL_EVENTS
7. AS
8. BEGIN
9. SET NOCOUNT ON;

11.DECLARE @data XML;

12.DECLARE @schema sysname;

13.DECLARE @object sysname;

14.DECLARE @eventType sysname;

15.DECLARE @tableHTML NVARCHAR(MAX) ;

17.SET @data = EVENTDATA();

18.SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');

19.SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');

20.SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

22.IF @object IS NOT NULL

23.PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;

24.ELSE

25.PRINT ' ' + @eventType + ' - ' + @schema;

27.IF @eventType IS NULL

28.PRINT CONVERT(nvarchar(max), @data);

30.INSERT [msdb].[dbo].[DatabaseLog]

31.(

32.[PostTime],

33.[DatabaseUser],

34.[LoginName],

35.[ClientHost],

36.[Event],

37.[Schema],

38.[Object],

39.[TSQL],

40.[XmlEvent]

41.)

42.VALUES

43.(

44.GETDATE(),

45.CONVERT(sysname, CURRENT_USER),

46.@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)'),

47.CONVERT(sysname, HOST_NAME()),

48.@eventType,

49.CONVERT(sysname, @schema),

50.CONVERT(sysname, @object),

51.@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),

52.@data

53.);

55.SET @tableHTML =

56.N'<H1>DDL Event</H1>' +

57.N'<table border="0">' +

58.N'<tr><th>Post Time</th><th>User</th><th>Login</th><th>ClientHost</th>' +

59.N'<th>TSQL</th><th></tr>' +

60.CAST(( SELECT

61.td = PostTime, '',

62.td = DatabaseUser, '',

63.td = LoginName, '',

64.td = ClientHost, '',

65.td = TSQL, ''

66.FROM msdb.dbo.DatabaseLog

67.WHERE DatabaseLogID =(select max(DatabaseLogID) from msdb.dbo.DatabaseLog)

68.FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ;

70.EXEC msdb.dbo.sp_send_dbmail

71.@profile_name = 'DataBase_DDL_Event',

72.@recipients='***@***.com',

73.@subject = 'DDL Event - DataBase MyAssistant',

74.@body = @tableHTML,

75.@body_format = 'HTML' ;

76.END;

80.GO

接下来我们来测试一下，假如一个用户Test登录数据库，一不小心删除了一个Test的表，如下图一所示，那么我将收到一封邮件，提示我用户Test在那台客户端主机执行了啥DDL操作(如下图二所示)，当然邮件的样式、排版有兴趣的可以去美化一下。

 

GPS平台、网站建设、软件开发、系统运维，找森大网络科技！
https://cnsendnet.taobao.com
来自森大科技官方博客
http://www.cnsendblog.com/index.php/?p=1778