Home Database Mysql Tutorial MS SQL监控数据库的DDL操作

MS SQL监控数据库的DDL操作

Jun 07, 2016 pm 05:44 PM
operate database monitor

前言: 有时候,一个数据库有多个帐号,包括数据库管理员,开发人员,运维支撑人员等,可能有很多帐号都有比较大的权限,例如DDL操作权限(创建,修改,删除存储过程,创建,修改,删除表等),账户多了,管理起来就会相当麻烦,容易产生混乱,如果数据库管理

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

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

SQL Code 1

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

SQL Code 2

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

 

clipboard

clipboard[1]

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How does Go language implement the addition, deletion, modification and query operations of the database? How does Go language implement the addition, deletion, modification and query operations of the database? Mar 27, 2024 pm 09:39 PM

How does Go language implement the addition, deletion, modification and query operations of the database?

Detailed tutorial on establishing a database connection using MySQLi in PHP Detailed tutorial on establishing a database connection using MySQLi in PHP Jun 04, 2024 pm 01:42 PM

Detailed tutorial on establishing a database connection using MySQLi in PHP

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos Jul 18, 2024 am 05:48 AM

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos

How does Hibernate implement polymorphic mapping? How does Hibernate implement polymorphic mapping? Apr 17, 2024 pm 12:09 PM

How does Hibernate implement polymorphic mapping?

How to bind WeChat on Ele.me How to bind WeChat on Ele.me Apr 01, 2024 pm 03:46 PM

How to bind WeChat on Ele.me

An in-depth analysis of how HTML reads the database An in-depth analysis of how HTML reads the database Apr 09, 2024 pm 12:36 PM

An in-depth analysis of how HTML reads the database

Go language practical guide: Detailed explanation of string deletion operation Go language practical guide: Detailed explanation of string deletion operation Mar 27, 2024 am 10:54 AM

Go language practical guide: Detailed explanation of string deletion operation

Forgot your Win8 computer startup password? This operation will restore it immediately! Forgot your Win8 computer startup password? This operation will restore it immediately! Mar 27, 2024 pm 10:12 PM

Forgot your Win8 computer startup password? This operation will restore it immediately!

See all articles