27,579
社区成员
发帖
与我相关
我的任务
分享
<EVENT_INSTANCE>
<EventType>GRANT_DATABASE</EventType>
<PostTime>2013-09-22T15:43:44.133</PostTime>
<SPID>52</SPID>
<ServerName>GGG-PC</ServerName>
<LoginName>ggg-PC\Administrator</LoginName>
<UserName>dbo</UserName>
<DatabaseName>ggg</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>wcT</ObjectName>
<ObjectType>TABLE</ObjectType>
<Grantor>dbo</Grantor>
<Permissions>
<Permission>select</Permission>
</Permissions>
<Grantees>
<Grantee>public</Grantee>
</Grantees>
<AsGrantor />
<GrantOption>0</GrantOption>
<CascadeOption>0</CascadeOption>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>grant select on ggg.dbo.wct to public</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
你可以进一步解析。
--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 '%grant%' or
type_name like '%deny%' or
type_name like '%revoke%'
*/
--2.2建立服务器级别触发器
create trigger gyy_server
on all server
for GRANT_SERVER,
DENY_SERVER,
REVOKE_SERVER,
GRANT_DATABASE,
DENY_DATABASE,
REVOKE_DATABASE
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
grant select on wc.dbo.wc_table to public
go
--查看记录的事件
select 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