現(xiàn)在來解決這個(gè)問題的方案，我們通過創(chuàng)建一個(gè)表DatabaseLog和DDL觸發(fā)器來解決問題，首先在msdb數(shù)據(jù)庫里面新建一個(gè)表DatabaseLog，用來保存DDL觸發(fā)器獲取的信息。其中DDL觸發(fā)器主要通過EVENTDATA()函數(shù)返回有關(guān)服務(wù)器或數(shù)據(jù)庫事件的信息。

有時(shí)候，一個(gè)數(shù)據(jù)庫有多個(gè)帳號(hào)，包括數(shù)據(jù)庫管理員，開發(fā)人員，運(yùn)維支撐人員等，可能有很多帳號(hào)都有比較大的權(quán)限，例如DDL操作權(quán)限(創(chuàng)建，修改，刪除存儲(chǔ)過程，創(chuàng)建，修改，刪除表等），賬戶多了，管理起來就會(huì)相當(dāng)麻煩，容易產(chǎn)生混亂，如果數(shù)據(jù)庫管理員不監(jiān)控?cái)?shù)據(jù)庫架構(gòu)變更的話，就不知道誰對(duì)數(shù)據(jù)庫架構(gòu)做了啥改動(dòng)（此處改動(dòng)僅僅只DDL操作），尤其有時(shí)候，有些開發(fā)人員可能不按規(guī)章制度辦事，繞過或忘了通知發(fā)布人員或DBA，直接去生產(chǎn)機(jī)做一些DDL操作，那么我們就需要對(duì)數(shù)據(jù)庫架構(gòu)某些更改的事件進(jìn)行監(jiān)控，如果能夠監(jiān)控并留下證據(jù)，這樣既可以讓DBA或相關(guān)管理人員知曉這些變更，有效管理數(shù)據(jù)庫，也可以避免出現(xiàn)問題，出現(xiàn)扯皮現(xiàn)象，最后DBA成了背黑鍋的。

MS SQL數(shù)據(jù)庫的DDL操作--創(chuàng)建，修改，刪除表等監(jiān)控方法有效管理數(shù)據(jù)庫

SQL Code 1

USE msdb;

GO

CREATE TABLE [dbo].[DatabaseLog]

(

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

[PostTime] [datetime] NOT NULL,

[DatabaseUser] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[LoginName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ClientHost] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Event] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Schema] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Object] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

[XmlEvent] [xml] NOT NULL,

CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED

(

[DatabaseLogID] ASC

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

) ON [PRIMARY]

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

例如，我要監(jiān)控?cái)?shù)據(jù)庫MyAssistant的DDL操作，那么我們首先在“數(shù)據(jù)庫郵件”里面創(chuàng)建一個(gè)配置名為“ DataBase_DDL_Event”的配置文件（profile name）,這個(gè)就不多講了，不知道配置的，自己先練練手把，假如我需要讓數(shù)據(jù)庫把監(jiān)控到DDL操作變動(dòng)相信信息發(fā)送到我的郵箱 *****@***.com(用你自己的郵箱替代)，那么只需要修改下面代碼的郵箱和profile_name即可。

SQL Code 2

USE MyAssistant;

GO

CREATE TRIGGER [DTG_DatabaseDdlTriggerLog]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

BEGIN

SET NOCOUNT ON;

DECLARE @data XML;

DECLARE @schema sysname;

DECLARE @object sysname;

DECLARE @eventType sysname;

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @data = EVENTDATA();

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 + ' - ' + @schema + '.' + @object;

ELSE

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

IF @eventType IS NULL

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

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

(

[PostTime],

[DatabaseUser],

[LoginName],

[ClientHost],

[Event],

[Schema],

[Object],

[TSQL],

[XmlEvent]

)

VALUES

(

GETDATE(),

CONVERT(sysname, CURRENT_USER),

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

CONVERT(sysname, HOST_NAME()),

@eventType,

CONVERT(sysname, @schema),

CONVERT(sysname, @object),

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

@data

);

SET @tableHTML =

N'

DDL Event

' +

 

N'

' +

 

N'

' +

 

N'

' +

 

CAST(( SELECT

td = PostTime, '',

td = DatabaseUser, '',

td = LoginName, '',

td = ClientHost, '',

td = TSQL, ''

FROM msdb.dbo.DatabaseLog

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

FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'

Post Time	User	Login	ClientHost	TSQL

' ;

 

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DataBase_DDL_Event',

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

@subject = 'DDL Event - DataBase MyAssistant',

@body = @tableHTML,

@body_format = 'HTML' ;

END;

GO

接下來我們來測(cè)試一下，假如一個(gè)用戶Test登錄數(shù)據(jù)庫，一不小心刪除了一個(gè)Test的表，如下圖一所示，那么我將收到一封郵件，提示我用戶Test在那臺(tái)客戶端主機(jī)執(zhí)行了啥DDL操作(如下圖二所示)，當(dāng)然郵件的樣式、排版有興趣的可以去美化一下。