-- Capturing Login Commands
--创建示例数据库
CREATE DATABASE EventTracking
GO
USE EventTracking
GO
--创建队列
Create QUEUE SQLEventQueue WITH STATUS=ON;
GO
--创建服务,并关联到内建的事件通知约定
CREATE SERVICE [//AP4/TrackLoginModificationService]
ON QUEUE SQLEventQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
--对系统目录视图sys.databases进行查询
select service_broker_guid from sys.databases WHERE name = 'EventTracking'
/*/*------------------------
select service_broker_guid from sys.databases WHERE name = 'EventTracking'
------------------------*/
service_broker_guid
------------------------------------
D7B127E2-9FF0-4BFF-9D41-978F178553C9
(1 行受影响)*/
--在Server范围内创建事件通知来跟踪所有登录名的创建、修改和删除操作
----DROP EVENT NOTIFICATION EN_LoginEvents
----ON Server;
Create EVENT NOTIFICATION EN_LoginEvents ON SERVER FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
TO SERVICE '//AP4/TrackLoginModificationService','D7B127E2-9FF0-4BFF-9D41-978F178553C9';
--测试新的事件通知，创建一个登录名
----DROP login TrishelleN
----go
Create LOGIN TrishelleN WITH PASSWORD = 'AR!3i2ou4'
GO
--使用Select或Recieve(其中Recieve会删除队列中的事件消息)查询队列
/*------------------------
SELECT CAST(message_body as xml) EventInfo FROM dbo.SQLEventQueue
------------------------*/
EventInfo
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(0 行受影响)
为什么是空值 ? 能给分析原因出在哪里吗？
星光总能为我指引方向
