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

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

MS SQL數據庫的DDL操作--創建，修改，刪除表等監控方法有效管理數據庫

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

例如，我要監控數據庫MyAssistant的DDL操作，那么我們首先在“數據庫郵件”里面創建一個配置名為“ DataBase_DDL_Event”的配置文件（profile name）,這個就不多講了，不知道配置的，自己先練練手把，假如我需要讓數據庫把監控到DDL操作變動相信信息發送到我的郵箱 *****@***.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

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