如何监控sql server数据库记录被批量更新

我有几张表，数据量都比较大，千万级别，因工作需要，会有人经常使用公共的账户通过sql server认证方式连接到服务器进行update，一般情况下update只是根据需要更新个别记录，最近经常有人在执行update时忘记带上where条件，导致某张表的所有记录一次性全部被update掉了。

因工作需要，不太可能通过权限管理避免此类事故。所以想查到执行此类update动作的user对应的ip或者hostname，以儆效尤，如何实现此目标？

14 个解决方案

#1

方法是有不少，但是都有共同点——跟踪的数据会相当大，这个你能接受不？说不定一段时间之后跟踪的数据就比你的库还大了

#2

我是想知道为什么会允许非应用程序经常直接操作数据库并且执行这些危险的操作？开发库？

#3

建立一个触发器，记录谁在什么时候，做了修改

#4

不过就像上面说的，如果你的数据库有大量的update操作，那么很快，你的监控的记录就会很多的。

不过考虑到，楼主是想以儆效尤，所以，只需要监控的一个人的这种胡乱update的操作，估计其他人也不敢再随便操作了，那么就可以关闭监控。

#5

我觉得楼主监控公共账户所执行的所有sql语句就好了。
而且很容易拿到不带where条件的update语句。

#6

建议你这个处理是在系统中做成批量提交修改，然后每一次就相当于一次操作，在数据库中增加一张表，叫操作记录表，把这种用户的操作记录下来（当提交成功），这样后续都有据可查。

#7

用触发器记录表字段数据变化的历史记录,
参考 http://blog.csdn.net/ap0405140/article/details/8896330

#8

我只想说执行语句不带条件的都是牛人！！我就见过这样的，十几万的数据都要发给银行，那哥们直接全部给我更新了。

#9

感谢大家的回复，我也是打算创建触发器来监控update，只是，代价好像有点大了。

#10

因为绝大部分情况下都是正常的update操作，另外表如何很多的话，岂不是要逐个表创建触发器了？

有没有更好的办法呢？

#11

可以用sql trace，但是正如我上面说过，任何一种方式都以大量跟踪数据为代价。

#12

你查看Master.dbo.Sysprocesses 表，能发现线索
也可以判断，如果批量超过10条，就回滚，或者指定某台电脑才能执行一次超过10条的更新

#13

--要跟踪的表

if OBJECT_ID('wc') is not null

   drop table wc

go



create table wc(id int,vv varchar(10))


insert into wc

select 1,'abc' union all

select 2,'abc' union all

select 3,'abc'

go



--存放审核信息的表

if OBJECT_ID('audit_table') is not null

   drop table audit_table

go


create table audit_table

(

id int identity(1,1) primary key,


spid int,


connect_time DATETIME,

net_transport NVARCHAR(40),

protocol_type NVARCHAR(40),

auth_scheme NVARCHAR(40),

client_net_address VARCHAR(48),

client_tcp_port int,

local_net_address VARCHAR(48),

local_tcp_port int,


host_name NVARCHAR(128),

program_name NVARCHAR(128),

host_process_id int,

client_interface_name NVARCHAR(32),


exec_datetime datetime

)

go



--触发器

create trigger dbo.trigger_wc

on wc

after update

as


insert into audit_table

select  @@SPID,


        (select connect_time from sys.dm_exec_connections where session_id = @@spid),

		(select net_transport from sys.dm_exec_connections where session_id = @@spid),

		(select protocol_type from sys.dm_exec_connections where session_id = @@spid),

		(select auth_scheme from sys.dm_exec_connections where session_id = @@spid),

		(select client_net_address from sys.dm_exec_connections where session_id = @@spid),

		(select client_tcp_port from sys.dm_exec_connections where session_id = @@spid),

		(select local_net_address from sys.dm_exec_connections where session_id = @@spid),

		(select local_tcp_port from sys.dm_exec_connections where session_id = @@spid),


		(select [host_name] from sys.dm_exec_sessions where session_id = @@spid),

		(select [program_name] from sys.dm_exec_sessions where session_id = @@spid),

		(select host_process_id from sys.dm_exec_sessions where session_id = @@spid),

		(select client_interface_name from sys.dm_exec_sessions where session_id = @@spid),

        GETDATE()

go


--更新数据

update wc

set vv = 'aaa'



--查看记录

select *    

from audit_table

#14

最终还是选择了触发器，不过还是感谢大家的回复。

#1