主頁 > 知識庫 > MSSQL監(jiān)控?cái)?shù)據(jù)庫的DDL操作(創(chuàng)建,修改,刪除存儲過程,創(chuàng)建,修改,刪除表等)

MSSQL監(jiān)控?cái)?shù)據(jù)庫的DDL操作(創(chuàng)建,修改,刪除存儲過程,創(chuàng)建,修改,刪除表等)

熱門標(biāo)簽:競圣地圖標(biāo)注服務(wù) 智能語音車載電話機(jī)器人 云南全自動外呼系統(tǒng)公司 貴州外呼回?fù)芟到y(tǒng)是什么 智能外呼系統(tǒng)需要多少錢 陽光創(chuàng)信ai外呼獲客系統(tǒng)助力 衛(wèi)星地圖標(biāo)注距離 安陽天音防封電銷卡 外呼系統(tǒng)的話術(shù)
前言: 有時候,一個數(shù)據(jù)庫有多個帳號,包括數(shù)據(jù)庫管理員,開發(fā)人員,運(yùn)維支撐人員等,可能有很多帳號都有比較大的權(quán)限,例如DDL操作權(quán)限(創(chuàng)建,修改,刪除存儲過程,創(chuàng)建,修改,刪除表等),賬戶多了,管理起來就會相當(dāng)麻煩,容易產(chǎn)生混亂,如果數(shù)據(jù)庫管理員不監(jiān)控?cái)?shù)據(jù)庫架構(gòu)變更的話,就不知道誰對數(shù)據(jù)庫架構(gòu)做了啥改動(此處改動僅僅只DDL操作),尤其有時候,有些開發(fā)人員可能不按規(guī)章制度辦事,繞過或忘了通知發(fā)布人員或DBA,直接去生產(chǎn)機(jī)做一些DDL操作,那么我們就需要對數(shù)據(jù)庫架構(gòu)某些更改的事件進(jìn)行監(jiān)控,如果能夠監(jiān)控并留下證據(jù),這樣既可以讓DBA或相關(guān)管理人員知曉這些變更,有效管理數(shù)據(jù)庫,也可以避免出現(xiàn)問題,出現(xiàn)扯皮現(xiàn)象,最后DBA成了背黑鍋的。

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

復(fù)制代碼 代碼如下:

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)建一個配置名為“ DataBase_DDL_Event”的配置文件(profile name),這個就不多講了,不知道配置的,自己先練練手把,假如我需要讓數(shù)據(jù)庫把監(jiān)控到DDL操作變動相信信息發(fā)送到我的郵箱 *****@***.com(用你自己的郵箱替代),那么只需要修改下面代碼的郵箱和profile_name即可。

復(fù)制代碼 代碼如下:

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登錄數(shù)據(jù)庫,一不小心刪除了一個Test的表,如下圖一所示,那么我將收到一封郵件,提示我用戶Test在那臺客戶端主機(jī)執(zhí)行了啥DDL操作(如下圖二所示),當(dāng)然郵件的樣式、排版有興趣的可以去美化一下。

 

您可能感興趣的文章:
  • mysql數(shù)據(jù)庫基本語法及操作大全
  • 數(shù)據(jù)庫語言分類DDL、DCL、DML詳解
  • MySQL ddl語句的使用
  • Mysql DDL常見操作匯總
  • Mysql學(xué)習(xí)之創(chuàng)建和操作數(shù)據(jù)庫及表DDL大全小白篇

標(biāo)簽:鄂爾多斯 周口 預(yù)約服務(wù) 營口 寧夏 欽州 湘潭 河源

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MSSQL監(jiān)控?cái)?shù)據(jù)庫的DDL操作(創(chuàng)建,修改,刪除存儲過程,創(chuàng)建,修改,刪除表等)》,本文關(guān)鍵詞  MSSQL,監(jiān)控,數(shù)據(jù)庫,的,DDL,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MSSQL監(jiān)控?cái)?shù)據(jù)庫的DDL操作(創(chuàng)建,修改,刪除存儲過程,創(chuàng)建,修改,刪除表等)》相關(guān)的同類信息!
  • 本頁收集關(guān)于MSSQL監(jiān)控?cái)?shù)據(jù)庫的DDL操作(創(chuàng)建,修改,刪除存儲過程,創(chuàng)建,修改,刪除表等)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章