微软结构化查询语言数据库的数据定义语言操作：创建,修改,删除表（或存储过程)等监控方案

类型：社交聊天大小：13.5米语言：中文评分：4.4标签:立即下载有时候，一个数据库有多个帐号，包括数据库管理员，开发人员，运维支撑人员等，可能有很多帐号都有比较大的权限，例如数据定义语言操作权限（创建，修改，删除存储过程，创建，修改，删除表等),账户多了，管理起来就会相当麻烦，容易产生混乱，如果数据库管理员不监控数据库架构变更的话，就不知道谁对数据库架构做了啥改动（此处改动仅仅只数据定义语言操作),尤其有时候，有些开发人员可能不按规章制度办事，绕过或忘了通知发布人员或数据库管理员，直接去生产机做一些数据定义语言操作，那么我们就需要对数据库架构某些更改的事件进行监控，如果能够监控并留下证据，这样既可以让工商管理学博士(工商管理博士)或相关管理人员知晓这些变更，有效管理数据库，也可以避免出现问题，出现扯皮现象，最后工商管理学博士(工商管理博士)成了背黑锅的。

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

结构化查询语言代码一

USEmsdb

去

CREATETABLE[dbo].[数据库日志]

（

[DatabaseLogID][int]IDENTITY(1，1)NOTTNULL，

[后期时间][日期时间]不为空，

[数据库用户][系统名]COLLATESQL _ Latin1 _ General _ CP1 _ CI _ ASNOTNULL，

[LoginName][sysname]COLLATESQL _ Latin1 _ General _ CP1 _ CI _ ASNULL，

[客户端主机][系统名]COLLATESQL _ Latin1 _ General _ CP1 _ CI _ ASNULL，

[事件][系统名]COLLATESQL _ Latin1 _ General _ CP1 _ CI _ ASNOTNULL，

[Schema][sysname]COLLATESQL _ Latin1 _ General _ CP1 _ CI _ ASNULL，

[对象][系统名]COLLATESQL _ Latin1 _ General _ CP1 _ CI _ ASNULL，

[TSQL][nvarchar](max)COLLATESQL _ Latin1 _ General _ CP1 _ CI _ ASNOTNULL，

[XMl levent][XMl]NOTTNULL，

约束[PK _ DatabaseLog _ DatabaseLogID]PRIMARYKENONCCLUSTERED

（

[数据库登录]ASC

)带(PAD _ INDEX=关，IGNORE _ DUP _ KEY=关)开[主]

)开[主要]

去

exec sys。sp _ addextendedproperty @ name=N ' ms _ Description '，@ value=N '数据库日志记录的主键、@level0type=N'SCHEMA '、@level0name=N'dbo '、@level1type=N'TABLE '、@level1name=N'DatabaseLog '、@level2type=N'COLUMN '、@level2name=N'DatabaseLogID '

去

exec sys。sp _ addextendedproperty @ name=N ' ms _ Description '，@value=N '发生数据定义语言更改的日期和时间、@level0type=N'SCHEMA '、@level0name=N'dbo '、@level1type=N'TABLE '、@level1name=N'DatabaseLog '、@level2type=N'COLUMN '、@level2name=N'PostTime '

去

exec sys。sp _ addextendedproperty @ name=N ' ms _ Description '，@value=N '实现数据定义语言更改的用户、@level0type=N'SCHEMA '、@level0name=N'dbo '、@level1type=N'TABLE '、@level1name=N'DatabaseLog '、@level2type=N'COLUMN '、@ level2name=N 'DatabaseUser '

去

exec sys。sp _ addextendedproperty @ name=N ' ms _ Description '，@value=N '实现数据定义语言更改的登录名、@level0type=N'SCHEMA '、@level0name=N'dbo '、@level1type=N'TABLE '、@level1name=N'DatabaseLog '、@level2type=N'COLUMN '、@level2name=N'LoginName '

去

exec sys。sp _ addextendedproperty @ name=N ' ms _ Description '，@value=N '实现数据定义语言更改的客户端计算机、@level0type=N'SCHEMA '、@level0name=N'dbo '、@level1type=N'TABLE '、@level1name=N'DatabaseLog '、@level2type=N'COLUMN '、@level2name=N'ClientHost '

去

exec sys。sp _ addextendedproperty @ name=N ' ms _ Description '，@value=N '已执行的数据定义语言语句的类型、@level0type=N'SCHEMA '、@level0name=N'dbo '、@level1type=N'TABLE '、@level1name=N'DatabaseLog '、@level2type=N'COLUMN '、@level2name=N'Event '

去

execsys。sp _ addextendedproperty @ name=N ' ms _ Description '，@value=N '更改后的对象所属的架构、@level0type=N'SCHEMA '、@level0name=N'dbo '、@level1type=N'TABLE '、@level1name=N'DatabaseLog '、@level2type=N'COLUMN '、@level2name=N'Schema '

去

exec sys。sp _ addextendedproperty @ name=N ' ms _ Description '，@value=N '被数据定义语言语句更改的对象、@level0type=N'SCHEMA '、@level0name=N'dbo '、@level1type=N'TABLE '、@level1name=N'DatabaseLog '、@level2type=N'COLUMN '、@level2name=N'Object '

去

execsys。sp _ addextendedproperty @ name=N ' ms _ Description '，@value=N '执行的确切以原则为基础的管理检视语句、@level0type=N'SCHEMA '、@level0name=N'dbo '、@level1type=N'TABLE '、@level1name=N'DatabaseLog '、@level2type=N'COLUMN '、@level2name=N'TSQL '

去

execsys。sp _ addextendedproperty @ name=N ' ms _ Description '，@value=N '数据库触发器生成的原始可扩展置标语言数据、@level0type=N'SCHEMA '、@level0name=N'dbo '、@level1type=N'TABLE '、@level1name=N'DatabaseLog '、@level2type=N'COLUMN '、@level2name=N'XmlEvent '

去

exec sys。sp _ addextendedproperty @ name=N ' ms _ Description '，@value=N '跟踪对数据库进行的所有数据定义语言更改的审核表。数据由数据库触发器ddlddatabase triggerlog捕获、@level0type=N'SCHEMA '、@level0name=N'dbo '、@level1type=N'TABLE '、@level1name=N'DatabaseLog '

去

exec sys。sp _ addextendedproperty @ name=N ' ms _ Description '，@value=N '主键(非聚集)constraint '，@level0type=N'SCHEMA '，@level0name=N'dbo '，@level1type=N'TABLE '，@level1name=N'DatabaseLog '，@level2type=N'CONSTRAINT '，@ level 2 name=N ' PK _ DatabaseLog _ databaseloid '

去

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

结构化查询语言代码2

使用我的助手

去

CREATETRIGGER[DTG _ databasedtltriggerlog]

ONDATABASE

FORDDL_DATABASE_LEVEL_EVENTS

如同

开始

SETNOCOUNTON

DECLARE @ dataXML

DECLARE @ schemasysname

DECLARE @ objectsysname

DECLARE @ eventTypesysname

DECLARE @ tableHTMLNVARCHAR(MAX)；

SET @ data=EVENTDATA()；

SET @ EventType=@ data。值('(/EVENT _ INSTANCE/EventType)[1]'，' sysname ')；

SET @ schema=@ data。值('(/EVENT _ INSTANCE/SchemaName)[1]'，' sysname ')；

SET @ object=@ data。值((/EVENT _ INSTANCE/对象名)[1]'，' sysname ')

IF @ objectISNOTNULL

PRINT ' ' @ EventType ' '-' @ schema ' ' @对象

其他

打印' ' @事件类型' '-' @架构；

IF@eventTypeISNULL

PRINTCONVERT(nvarchar(max)，@ data)；

插入[msdb].[dbo].[数据库日志]

（

[PostTime]，

[数据库用户],

[登录名称],

[ClientHost]，

[事件],

[模式],

[对象],

[TSQL]，

[XmlEvent]

)

价值观念

（

GETDATE()，

CONVERT(sysname，CURRENT_USER)，

@ data。值('(/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)')，

@数据

);

SET@tableHTML=

N'H1DDL事件/H1

不表格边框='0 '

'第n次开机自检时间/第/第登录/第客户端主机/第'

N'thTSQL/thth/tr '

CAST(选择

td=PostTime，'，

td=数据库用户'',

td=LoginName ' '，

td=ClientHost ' '，

td=TSQL，''

FROMmsdb.dbo.DatabaseLog

WHEREDatabaseLogID=(从msdb.dbo.DatabaseLog中选择max(DatabaseLogID))

FORXMLPATH('tr ')，TYPE)ASNVARCHAR(MAX))N '/table '；

execsdb。dbo。sp _ send _ dbmail

@ profile _ name=' DatabaSe _ DDL _ Event '，

@收件人='***@***。'' com '，

@subject='DDL事件- DataBase MyAssistant '，

@body=@tableHTML，

@ body _ format=' HTML

结束

去

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