/**************************downmoon 3w@live.cn***********************/
-- Capturing Login Commands
--创建示例数据库
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'EventTracking')
drop database EventTracking
GO
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'
/*
service_broker_guid
18FD2712-E551-4B6A-BC88-58E16D8D5BCD
*/
--在Server范围内创建事件通知来跟踪所有登录名的创建、修改和删除操作
Create EVENT NOTIFICATION EN_LoginEvents
ON SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
TO SERVICE '//AP4/TrackLoginModificationService',
'18FD2712-E551-4B6A-BC88-58E16D8D5BCD';
--测试新的事件通知,创建一个登录名
----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
/*