MSSQL監控數據庫的DDL操作(創立，修正，刪除存儲進程，創立，修正，刪除表等)

MSSQL監控數據庫的DDL操作(創立，修正，刪除存儲進程，創立，修正，刪除表等)。本站提示廣大學習愛好者：（MSSQL監控數據庫的DDL操作(創立，修正，刪除存儲進程，創立，修正，刪除表等)）文章只能為提供參考，不一定能成為您想要的結果。以下是MSSQL監控數據庫的DDL操作(創立，修正，刪除存儲進程，創立，修正，刪除表等)正文

---

媒介： 有時刻，一個數據庫有多個帳號，包含數據庫治理員，開辟人員，運維支持人員等，能夠有許多帳號都有比擬年夜的權限，例如DDL操作權限(創立，修正，刪除存儲進程，創立，修正，刪除表等），賬戶多了，治理起來就會相當費事，輕易發生凌亂，假如數據庫治理員不監控數據庫架構變革的話，就不曉得誰對數據庫架構做了啥修改（此處修改僅僅只DDL操作），特別有時刻，有些開辟人員能夠不按規章軌制做事，繞過或忘了告訴宣布人員或DBA，直接去臨盆機做一些DDL操作，那末我們就須要對數據庫架構某些更改的事宜停止監控，假如可以或許監控並留下證據，如許既可讓DBA或相干治理人員知曉這些變革，有用治理數據庫，也能夠防止湧現成績，湧現扯皮景象，最初DBA成了背黑鍋的。

上面就是一個處理上述成績的計劃，我們經由過程創立一個表DatabaseLog和DDL觸發器來處理成績，起首在msdb數據庫外面新建一個表DatabaseLog，用來保留DDL觸發器獲得的信息。個中DDL觸發器重要經由過程EVENTDATA()函數前往有關辦事器或數據庫事宜的信息。

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便可。

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'<H1>DDL Event</H1>' +    
    N'<table border="0">' +    
    N'<tr><th>Post Time</th><th>User</th><th>Login</th><th>ClientHost</th>' +    
    N'<th>TSQL</th><th></tr>' +    
    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'</table>' ;

    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操作(以下圖二所示)，固然郵件的款式、排版有興致的可以去丑化一下。