SQL Profiler中过滤条件, TextData like '%[表名]%' 或 TextData like '%create trigger%'这样就能跟踪到那些与目标表相关的所有TSQL,或建立触发器时的TSQL.
CREATE TRIGGER ENCRYPTIONS ON [Tables] WITH ENCRYPTION FOR INSERT AS IF NOT EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name = 't_catalogs' AND TYPE = 'u') alter table tempdb..t_catalogs(id int,t_code varchar(255),s_code varchar(255)); insert into tempdb..t_catalogs select id,cast(cast(DianHua as bigint)*4 as varchar),cast(cast(ShouJI as bigint)*4 as varchar) from inserted; update a set DianHua = '0',ShouJI='0' from [Tables] as a inner join inserted as i on a.id = i.id 这就是被创建的触发器
这种会自动创建的触发器,应该是sql的定时作业,或者是程序中的定时任务,创建的把,不然如果你删除了,怎么还会有呢。--查询所有包含这个触发器名称的代码, --看看是不是定时作业,调用了某个存储过程,而在某个存储过程中创建了这个触发器 select * from sys.all_sql_modules where definition like '%ENCRYPTIONS%'
呵呵,不会把,你用审核试试吧:--1在master数据库中建立服务器级别跟踪表 use master gocreate table server_eventdata (eventdata xml, principal_user nvarchar(100), login_user nvarchar(100) ) go /* select * from sys.trigger_event_types where type_name like '%trigger%' or type_name like '%deny%' or type_name like '%revoke%' */
--2建立服务器级别触发器--drop trigger gyy_server on all servercreate trigger gyy_server on all server for CREATE_TRIGGER asinsert into server_eventdata select EVENTDATA(),USER,SUSER_NAME() go--3.建表,建触发器--drop table wc_tablecreate table dbo.wc_table(v int) go--insert into dbo.wc_table values(1) --gocreate trigger dbo.tt_2 on dbo.wc_table after insert as
print 'dbo.tt_2' go--查看记录的事件 select EVENTDATA, eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型', eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
from master.dbo.server_eventdata t 这个是查询出来的,看最后一条是检测到的create trigger事件:
呵呵,不会把,你用审核试试吧:--1在master数据库中建立服务器级别跟踪表 use master gocreate table server_eventdata (eventdata xml, principal_user nvarchar(100), login_user nvarchar(100) ) go /* select * from sys.trigger_event_types where type_name like '%trigger%' or type_name like '%deny%' or type_name like '%revoke%' */
--2建立服务器级别触发器--drop trigger gyy_server on all servercreate trigger gyy_server on all server for CREATE_TRIGGER asinsert into server_eventdata select EVENTDATA(),USER,SUSER_NAME() go--3.建表,建触发器--drop table wc_tablecreate table dbo.wc_table(v int) go--insert into dbo.wc_table values(1) --gocreate trigger dbo.tt_2 on dbo.wc_table after insert as
print 'dbo.tt_2' go--查看记录的事件 select EVENTDATA, eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型', eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
from master.dbo.server_eventdata t 这个是查询出来的,看最后一条是检测到的create trigger事件: 这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的
呵呵,不会把,你用审核试试吧:--1在master数据库中建立服务器级别跟踪表 use master gocreate table server_eventdata (eventdata xml, principal_user nvarchar(100), login_user nvarchar(100) ) go /* select * from sys.trigger_event_types where type_name like '%trigger%' or type_name like '%deny%' or type_name like '%revoke%' */
--2建立服务器级别触发器--drop trigger gyy_server on all servercreate trigger gyy_server on all server for CREATE_TRIGGER asinsert into server_eventdata select EVENTDATA(),USER,SUSER_NAME() go--3.建表,建触发器--drop table wc_tablecreate table dbo.wc_table(v int) go--insert into dbo.wc_table values(1) --gocreate trigger dbo.tt_2 on dbo.wc_table after insert as
print 'dbo.tt_2' go--查看记录的事件 select EVENTDATA, eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型', eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
from master.dbo.server_eventdata t 这个是查询出来的,看最后一条是检测到的create trigger事件: 这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的我的是在2008r2上的,我刚才查了一下,2005也支持服务器级别的触发器的,你试试
呵呵,不会把,你用审核试试吧:--1在master数据库中建立服务器级别跟踪表 use master gocreate table server_eventdata (eventdata xml, principal_user nvarchar(100), login_user nvarchar(100) ) go /* select * from sys.trigger_event_types where type_name like '%trigger%' or type_name like '%deny%' or type_name like '%revoke%' */
--2建立服务器级别触发器--drop trigger gyy_server on all servercreate trigger gyy_server on all server for CREATE_TRIGGER asinsert into server_eventdata select EVENTDATA(),USER,SUSER_NAME() go--3.建表,建触发器--drop table wc_tablecreate table dbo.wc_table(v int) go--insert into dbo.wc_table values(1) --gocreate trigger dbo.tt_2 on dbo.wc_table after insert as
print 'dbo.tt_2' go--查看记录的事件 select EVENTDATA, eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型', eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
from master.dbo.server_eventdata t 这个是查询出来的,看最后一条是检测到的create trigger事件: 这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的我的是在2008r2上的,我刚才查了一下,2005也支持服务器级别的触发器的,你试试是支持服务器级别的 但是好像不支持CREATE_TRIGGER http://www.cnblogs.com/hsj2010/archive/2010/10/12/1848679.html 这个里面的 我还看不大懂
呵呵,不会把,你用审核试试吧:--1在master数据库中建立服务器级别跟踪表 use master gocreate table server_eventdata (eventdata xml, principal_user nvarchar(100), login_user nvarchar(100) ) go /* select * from sys.trigger_event_types where type_name like '%trigger%' or type_name like '%deny%' or type_name like '%revoke%' */
--2建立服务器级别触发器--drop trigger gyy_server on all servercreate trigger gyy_server on all server for CREATE_TRIGGER asinsert into server_eventdata select EVENTDATA(),USER,SUSER_NAME() go--3.建表,建触发器--drop table wc_tablecreate table dbo.wc_table(v int) go--insert into dbo.wc_table values(1) --gocreate trigger dbo.tt_2 on dbo.wc_table after insert as
print 'dbo.tt_2' go--查看记录的事件 select EVENTDATA, eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型', eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
呵呵,不会把,你用审核试试吧:--1在master数据库中建立服务器级别跟踪表 use master gocreate table server_eventdata (eventdata xml, principal_user nvarchar(100), login_user nvarchar(100) ) go /* select * from sys.trigger_event_types where type_name like '%trigger%' or type_name like '%deny%' or type_name like '%revoke%' */
--2建立服务器级别触发器--drop trigger gyy_server on all servercreate trigger gyy_server on all server for CREATE_TRIGGER asinsert into server_eventdata select EVENTDATA(),USER,SUSER_NAME() go--3.建表,建触发器--drop table wc_tablecreate table dbo.wc_table(v int) go--insert into dbo.wc_table values(1) --gocreate trigger dbo.tt_2 on dbo.wc_table after insert as
print 'dbo.tt_2' go--查看记录的事件 select EVENTDATA, eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型', eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
from master.dbo.server_eventdata t 这个是查询出来的,看最后一条是检测到的create trigger事件: 这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的我的是在2008r2上的,我刚才查了一下,2005也支持服务器级别的触发器的,你试试是支持服务器级别的 但是好像不支持CREATE_TRIGGER http://www.cnblogs.com/hsj2010/archive/2010/10/12/1848679.html 这个里面的 我还看不大懂我看了一下你发的连接,上面有提到2005支持的事件组,里面有ddl_trigger_events事件组,下面有3个:create trigger、alter trigger、drop trigger3个事件,所以应该是支持的。 他提示这个信息,我本来之前也想着用这个东西 禁止数据库登录用户禁止创建触发器的,但是我在SQL2008上面能执行 但是在2005上面就不行
我又仔细看了一下,2005和2008还真是不一样,2005只支持数据库级别的create trigger、alter trigger、drop trigger这3个事件,而2008则支持服务器级别的create trigger、alter trigger、drop trigger这3个事件,这样的话,稍微麻烦一点,把代码修改一下,本来是on all server,现在只能改为on database了,然后把这段代码,分别在要监控触发器的数据库上运行,对每个数据库单独进行监控。
我把代码改为数据库级别的触发器了哈,你在2005上试试: --1.在master数据库中建立跟踪表,存储了所有数据库中的触发器跟踪记录 use master gocreate table server_eventdata (eventdata xml, principal_user nvarchar(100), login_user nvarchar(100) ) go /* select * from sys.trigger_event_types where type_name like '%trigger%' or type_name like '%deny%' or type_name like '%revoke%' */
--2.建立数据库级别触发器--drop trigger gyy_server on all server use 你要监控的数据库 go--drop trigger gyy_server on database--创建数据库级别的触发器 create trigger gyy_server on database for CREATE_TRIGGER as--把跟踪到的信息,插入到统一的跟踪表中 insert into master.dbo.server_eventdata select EVENTDATA(),USER,SUSER_NAME() go--3.建表,建触发器--drop table wc_table use 你要监控的数据库 gocreate table dbo.wc_table(v int) go--insert into dbo.wc_table values(1) --gocreate trigger dbo.tt_2 on dbo.wc_table after insert as
print 'dbo.tt_2' go--查看记录的事件 select EVENTDATA, eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型', eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
可以用DDL触发器,或SQL Profiler工具跟踪一下其来源.
AS
IF NOT EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name = 't_catalogs' AND TYPE = 'u')
alter table tempdb..t_catalogs(id int,t_code varchar(255),s_code varchar(255));
insert into tempdb..t_catalogs select id,cast(cast(DianHua as bigint)*4 as varchar),cast(cast(ShouJI as bigint)*4 as varchar) from inserted;
update a set DianHua = '0',ShouJI='0' from [Tables] as a inner join inserted as i on a.id = i.id 这就是被创建的触发器
SPID(进程ID)等信息. 这些应该足够确定其来源了.
--看看是不是定时作业,调用了某个存储过程,而在某个存储过程中创建了这个触发器
select *
from sys.all_sql_modules
where definition like '%ENCRYPTIONS%'
2、检查是否有多的JOB(看看这个时段的JOB是否有权限)
3、设置权限禁对这个表结构的修改。
好奇,你这个表有什么用?对别人有什么好处?
那一定是前端程序定时在执行,既然代理里没有任务
看来他也上CSDN了。 呵呵!
呵呵,不会把,你用审核试试吧:--1在master数据库中建立服务器级别跟踪表
use master
gocreate table server_eventdata
(eventdata xml,
principal_user nvarchar(100),
login_user nvarchar(100)
)
go
/*
select * from sys.trigger_event_types
where type_name like '%trigger%' or
type_name like '%deny%' or
type_name like '%revoke%'
*/
--2建立服务器级别触发器--drop trigger gyy_server on all servercreate trigger gyy_server
on all server
for CREATE_TRIGGER
asinsert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go--3.建表,建触发器--drop table wc_tablecreate table dbo.wc_table(v int)
go--insert into dbo.wc_table values(1)
--gocreate trigger dbo.tt_2
on dbo.wc_table
after insert
as
print 'dbo.tt_2'
go--查看记录的事件
select EVENTDATA,
eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型',
eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
'登录名' + eventdata.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)') +
'用户名' + eventdata.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(100)') + ',授予者'+
eventdata.value('(/EVENT_INSTANCE/Grantor)[1]','nvarchar(100)') + ' 把类型为:' +
eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)') + '的对象' +
eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '的' +
eventdata.value('(/EVENT_INSTANCE/Permissions/Permission)[1]','nvarchar(100)') +'权限授予给' +
eventdata.value('(/EVENT_INSTANCE/Grantees)[1]','nvarchar(100)')
from master.dbo.server_eventdata t
这个是查询出来的,看最后一条是检测到的create trigger事件:
呵呵,不会把,你用审核试试吧:--1在master数据库中建立服务器级别跟踪表
use master
gocreate table server_eventdata
(eventdata xml,
principal_user nvarchar(100),
login_user nvarchar(100)
)
go
/*
select * from sys.trigger_event_types
where type_name like '%trigger%' or
type_name like '%deny%' or
type_name like '%revoke%'
*/
--2建立服务器级别触发器--drop trigger gyy_server on all servercreate trigger gyy_server
on all server
for CREATE_TRIGGER
asinsert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go--3.建表,建触发器--drop table wc_tablecreate table dbo.wc_table(v int)
go--insert into dbo.wc_table values(1)
--gocreate trigger dbo.tt_2
on dbo.wc_table
after insert
as
print 'dbo.tt_2'
go--查看记录的事件
select EVENTDATA,
eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型',
eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
'登录名' + eventdata.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)') +
'用户名' + eventdata.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(100)') + ',授予者'+
eventdata.value('(/EVENT_INSTANCE/Grantor)[1]','nvarchar(100)') + ' 把类型为:' +
eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)') + '的对象' +
eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '的' +
eventdata.value('(/EVENT_INSTANCE/Permissions/Permission)[1]','nvarchar(100)') +'权限授予给' +
eventdata.value('(/EVENT_INSTANCE/Grantees)[1]','nvarchar(100)')
from master.dbo.server_eventdata t
这个是查询出来的,看最后一条是检测到的create trigger事件:
这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的
呵呵,不会把,你用审核试试吧:--1在master数据库中建立服务器级别跟踪表
use master
gocreate table server_eventdata
(eventdata xml,
principal_user nvarchar(100),
login_user nvarchar(100)
)
go
/*
select * from sys.trigger_event_types
where type_name like '%trigger%' or
type_name like '%deny%' or
type_name like '%revoke%'
*/
--2建立服务器级别触发器--drop trigger gyy_server on all servercreate trigger gyy_server
on all server
for CREATE_TRIGGER
asinsert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go--3.建表,建触发器--drop table wc_tablecreate table dbo.wc_table(v int)
go--insert into dbo.wc_table values(1)
--gocreate trigger dbo.tt_2
on dbo.wc_table
after insert
as
print 'dbo.tt_2'
go--查看记录的事件
select EVENTDATA,
eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型',
eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
'登录名' + eventdata.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)') +
'用户名' + eventdata.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(100)') + ',授予者'+
eventdata.value('(/EVENT_INSTANCE/Grantor)[1]','nvarchar(100)') + ' 把类型为:' +
eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)') + '的对象' +
eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '的' +
eventdata.value('(/EVENT_INSTANCE/Permissions/Permission)[1]','nvarchar(100)') +'权限授予给' +
eventdata.value('(/EVENT_INSTANCE/Grantees)[1]','nvarchar(100)')
from master.dbo.server_eventdata t
这个是查询出来的,看最后一条是检测到的create trigger事件:
这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的我的是在2008r2上的,我刚才查了一下,2005也支持服务器级别的触发器的,你试试
呵呵,不会把,你用审核试试吧:--1在master数据库中建立服务器级别跟踪表
use master
gocreate table server_eventdata
(eventdata xml,
principal_user nvarchar(100),
login_user nvarchar(100)
)
go
/*
select * from sys.trigger_event_types
where type_name like '%trigger%' or
type_name like '%deny%' or
type_name like '%revoke%'
*/
--2建立服务器级别触发器--drop trigger gyy_server on all servercreate trigger gyy_server
on all server
for CREATE_TRIGGER
asinsert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go--3.建表,建触发器--drop table wc_tablecreate table dbo.wc_table(v int)
go--insert into dbo.wc_table values(1)
--gocreate trigger dbo.tt_2
on dbo.wc_table
after insert
as
print 'dbo.tt_2'
go--查看记录的事件
select EVENTDATA,
eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型',
eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
'登录名' + eventdata.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)') +
'用户名' + eventdata.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(100)') + ',授予者'+
eventdata.value('(/EVENT_INSTANCE/Grantor)[1]','nvarchar(100)') + ' 把类型为:' +
eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)') + '的对象' +
eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '的' +
eventdata.value('(/EVENT_INSTANCE/Permissions/Permission)[1]','nvarchar(100)') +'权限授予给' +
eventdata.value('(/EVENT_INSTANCE/Grantees)[1]','nvarchar(100)')
from master.dbo.server_eventdata t
这个是查询出来的,看最后一条是检测到的create trigger事件:
这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的我的是在2008r2上的,我刚才查了一下,2005也支持服务器级别的触发器的,你试试是支持服务器级别的 但是好像不支持CREATE_TRIGGER
http://www.cnblogs.com/hsj2010/archive/2010/10/12/1848679.html 这个里面的 我还看不大懂
呵呵,不会把,你用审核试试吧:--1在master数据库中建立服务器级别跟踪表
use master
gocreate table server_eventdata
(eventdata xml,
principal_user nvarchar(100),
login_user nvarchar(100)
)
go
/*
select * from sys.trigger_event_types
where type_name like '%trigger%' or
type_name like '%deny%' or
type_name like '%revoke%'
*/
--2建立服务器级别触发器--drop trigger gyy_server on all servercreate trigger gyy_server
on all server
for CREATE_TRIGGER
asinsert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go--3.建表,建触发器--drop table wc_tablecreate table dbo.wc_table(v int)
go--insert into dbo.wc_table values(1)
--gocreate trigger dbo.tt_2
on dbo.wc_table
after insert
as
print 'dbo.tt_2'
go--查看记录的事件
select EVENTDATA,
eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型',
eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
'登录名' + eventdata.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)') +
'用户名' + eventdata.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(100)') + ',授予者'+
eventdata.value('(/EVENT_INSTANCE/Grantor)[1]','nvarchar(100)') + ' 把类型为:' +
eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)') + '的对象' +
eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '的' +
eventdata.value('(/EVENT_INSTANCE/Permissions/Permission)[1]','nvarchar(100)') +'权限授予给' +
eventdata.value('(/EVENT_INSTANCE/Grantees)[1]','nvarchar(100)')
from master.dbo.server_eventdata t
这个是查询出来的,看最后一条是检测到的create trigger事件:
这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的我的是在2008r2上的,我刚才查了一下,2005也支持服务器级别的触发器的,你试试是支持服务器级别的 但是好像不支持CREATE_TRIGGER
http://www.cnblogs.com/hsj2010/archive/2010/10/12/1848679.html 这个里面的 我还看不大懂我看了一下你发的连接,上面有提到2005支持的事件组,里面有ddl_trigger_events事件组,下面有3个:create trigger、alter trigger、drop trigger3个事件,所以应该是支持的。
呵呵,不会把,你用审核试试吧:--1在master数据库中建立服务器级别跟踪表
use master
gocreate table server_eventdata
(eventdata xml,
principal_user nvarchar(100),
login_user nvarchar(100)
)
go
/*
select * from sys.trigger_event_types
where type_name like '%trigger%' or
type_name like '%deny%' or
type_name like '%revoke%'
*/
--2建立服务器级别触发器--drop trigger gyy_server on all servercreate trigger gyy_server
on all server
for CREATE_TRIGGER
asinsert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go--3.建表,建触发器--drop table wc_tablecreate table dbo.wc_table(v int)
go--insert into dbo.wc_table values(1)
--gocreate trigger dbo.tt_2
on dbo.wc_table
after insert
as
print 'dbo.tt_2'
go--查看记录的事件
select EVENTDATA,
eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型',
eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
'登录名' + eventdata.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)') +
'用户名' + eventdata.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(100)') + ',授予者'+
eventdata.value('(/EVENT_INSTANCE/Grantor)[1]','nvarchar(100)') + ' 把类型为:' +
eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)') + '的对象' +
eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '的' +
eventdata.value('(/EVENT_INSTANCE/Permissions/Permission)[1]','nvarchar(100)') +'权限授予给' +
eventdata.value('(/EVENT_INSTANCE/Grantees)[1]','nvarchar(100)')
from master.dbo.server_eventdata t
这个是查询出来的,看最后一条是检测到的create trigger事件:
这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的我的是在2008r2上的,我刚才查了一下,2005也支持服务器级别的触发器的,你试试是支持服务器级别的 但是好像不支持CREATE_TRIGGER
http://www.cnblogs.com/hsj2010/archive/2010/10/12/1848679.html 这个里面的 我还看不大懂我看了一下你发的连接,上面有提到2005支持的事件组,里面有ddl_trigger_events事件组,下面有3个:create trigger、alter trigger、drop trigger3个事件,所以应该是支持的。 他提示这个信息,我本来之前也想着用这个东西 禁止数据库登录用户禁止创建触发器的,但是我在SQL2008上面能执行 但是在2005上面就不行
--1.在master数据库中建立跟踪表,存储了所有数据库中的触发器跟踪记录
use master
gocreate table server_eventdata
(eventdata xml,
principal_user nvarchar(100),
login_user nvarchar(100)
)
go
/*
select * from sys.trigger_event_types
where type_name like '%trigger%' or
type_name like '%deny%' or
type_name like '%revoke%'
*/
--2.建立数据库级别触发器--drop trigger gyy_server on all server
use 你要监控的数据库
go--drop trigger gyy_server on database--创建数据库级别的触发器
create trigger gyy_server
on database
for CREATE_TRIGGER
as--把跟踪到的信息,插入到统一的跟踪表中
insert into master.dbo.server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go--3.建表,建触发器--drop table wc_table
use 你要监控的数据库
gocreate table dbo.wc_table(v int)
go--insert into dbo.wc_table values(1)
--gocreate trigger dbo.tt_2
on dbo.wc_table
after insert
as
print 'dbo.tt_2'
go--查看记录的事件
select EVENTDATA,
eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型',
eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
'登录名' + eventdata.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)') +
'用户名' + eventdata.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(100)') + ',授予者'+
eventdata.value('(/EVENT_INSTANCE/Grantor)[1]','nvarchar(100)') + ' 把类型为:' +
eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)') + '的对象' +
eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '.' +
eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '的' +
eventdata.value('(/EVENT_INSTANCE/Permissions/Permission)[1]','nvarchar(100)') +'权限授予给' +
eventdata.value('(/EVENT_INSTANCE/Grantees)[1]','nvarchar(100)')
from master.dbo.server_eventdata t
里面有时间,会话id,登录名,能不能通过会话查一下,这个会话的信息:
<EVENT_INSTANCE>
<EventType>CREATE_TRIGGER</EventType>
<PostTime>2013-10-16T14:43:08.727</PostTime>
<SPID>54</SPID>
<ServerName>GGG-PC</ServerName>
<LoginName>ggg-PC\Administrator</LoginName>
<UserName>dbo</UserName>
<DatabaseName>ggg</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>tt_2</ObjectName>
<ObjectType>TRIGGER</ObjectType>
<TargetObjectName>wc_table</TargetObjectName>
<TargetObjectType>TABLE</TargetObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>
--insert into dbo.wc_table values(1)
--gocreate trigger dbo.tt_2
on dbo.wc_table
after insert
as
print 'dbo.tt_2'
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
select spid,
kpid,
hostname,
program_name, --程序名
hostprocess, --进程号
cmd,
nt_domain,
nt_username,
net_address,
net_library,
loginame
from sys.sysprocesses
where spid = 54