22,210
社区成员
发帖
与我相关
我的任务
分享
--2.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 '%table%' or
type_name like '%deny%' or
type_name like '%revoke%'
*/
--2.2建立服务器级别触发器
create trigger gyy_server
on all server
for create_table,
alter_table,
drop_table
as
insert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go
--2.3
create database wc
go
use wc
go
create table dbo.wc_table(v int)
insert into dbo.wc_table values(1)
go
use wc
go
alter table dbo.wc_table
add name varchar(100)
alter table dbo.wc_table
add tt datetime default getdate()
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/PostTime)[1]','datetime') as '执行时间',
'登录名' + 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
/*
事件类型 sql授权语句 执行时间 (无列名)
CREATE_TABLE create table dbo.wc_table(v int) 2013-11-07 10:22:31.157 NULL
ALTER_TABLE alter table dbo.wc_table add name varchar(100) 2013-11-07 10:23:59.987 NULL
ALTER_TABLE alter table dbo.wc_table add tt datetime default getdate() 2013-11-07 10:24:00.013 NULL
*/