22,207
社区成员
发帖
与我相关
我的任务
分享
--1在master数据库中建立服务器级别跟踪表
use master
go
create 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
create trigger gyy_server
on all server
for CREATE_TRIGGER
as
insert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go
--3.建表,建触发器
--drop table wc_table
create table dbo.wc_table(v int)
go
--insert into dbo.wc_table values(1)
--go
create 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
<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)
--go
create 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
--1.在master数据库中建立跟踪表,存储了所有数据库中的触发器跟踪记录
use master
go
create 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 你要监控的数据库
go
create table dbo.wc_table(v int)
go
--insert into dbo.wc_table values(1)
--go
create 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
--1在master数据库中建立服务器级别跟踪表
use master
go
create 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
create trigger gyy_server
on all server
for CREATE_TRIGGER
as
insert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go
--3.建表,建触发器
--drop table wc_table
create table dbo.wc_table(v int)
go
--insert into dbo.wc_table values(1)
--go
create 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
--1在master数据库中建立服务器级别跟踪表
use master
go
create 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
create trigger gyy_server
on all server
for CREATE_TRIGGER
as
insert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go
--3.建表,建触发器
--drop table wc_table
create table dbo.wc_table(v int)
go
--insert into dbo.wc_table values(1)
--go
create 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事件:
[/quote] 这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的[/quote]
我的是在2008r2上的,我刚才查了一下,2005也支持服务器级别的触发器的,你试试[/quote]是支持服务器级别的 但是好像不支持CREATE_TRIGGER
http://www.cnblogs.com/hsj2010/archive/2010/10/12/1848679.html 这个里面的 我还看不大懂[/quote]
我看了一下你发的连接,上面有提到2005支持的事件组,里面有ddl_trigger_events事件组,下面有3个:create trigger、alter trigger、drop trigger3个事件,所以应该是支持的。
--1在master数据库中建立服务器级别跟踪表
use master
go
create 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
create trigger gyy_server
on all server
for CREATE_TRIGGER
as
insert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go
--3.建表,建触发器
--drop table wc_table
create table dbo.wc_table(v int)
go
--insert into dbo.wc_table values(1)
--go
create 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事件:
[/quote] 这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的[/quote]
我的是在2008r2上的,我刚才查了一下,2005也支持服务器级别的触发器的,你试试[/quote]是支持服务器级别的 但是好像不支持CREATE_TRIGGER
http://www.cnblogs.com/hsj2010/archive/2010/10/12/1848679.html 这个里面的 我还看不大懂
--1在master数据库中建立服务器级别跟踪表
use master
go
create 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
create trigger gyy_server
on all server
for CREATE_TRIGGER
as
insert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go
--3.建表,建触发器
--drop table wc_table
create table dbo.wc_table(v int)
go
--insert into dbo.wc_table values(1)
--go
create 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事件:
[/quote] 这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的[/quote]
我的是在2008r2上的,我刚才查了一下,2005也支持服务器级别的触发器的,你试试
--1在master数据库中建立服务器级别跟踪表
use master
go
create 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
create trigger gyy_server
on all server
for CREATE_TRIGGER
as
insert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go
--3.建表,建触发器
--drop table wc_table
create table dbo.wc_table(v int)
go
--insert into dbo.wc_table values(1)
--go
create 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事件:
[/quote] 这是在SQL 2008的上面执行的吗? 我这里的数据库是2005的