enable sql server notification sql 的通知功能需要的最小权限

原文：http://hi.baidu.com/cairabbit/blog

use XXXX

-- configure database to enable .net clr support
EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
go
-- enable database using broker
ALTER DATABASE XXXX SET ENABLE_BROKER
go
-- set database trustworthy
ALTER DATABASE XXXX SET TRUSTWORTHY ON
go
-- you can check if the database broker enabled
--select is_broker_enabled from sys.databases where name = 'XXXX'

---- add notification accounts, if the application logged the database as sa,
---- it's not necessary to create these accounts.

CREATE USER [notifier] FOR LOGIN [sa]
WITH DEFAULT_SCHEMA = [notification]
GO
CREATE SCHEMA [notification] AUTHORIZATION [notifier]
GO

EXEC sp_addrole 'sql_dependency_subscriber'
GO

-- grant privilege to the login user (if you are logged in as sa(dbo), this is not necessary)
GRANT CREATE PROCEDURE TO sql_dependency_subscriber
GRANT CREATE QUEUE TO sql_dependency_subscriber
GRANT CREATE SERVICE TO sql_dependency_subscriber
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to sql_dependency_subscriber
GRANT VIEW DEFINITION TO sql_dependency_subscriber
GRANT SELECT to sql_dependency_subscriber

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sql_dependency_subscriber
GRANT RECEIVE ON QueryNotificationErrorsQueue TO sql_dependency_subscriber
GO

EXEC sp_addrolemember 'sql_dependency_subscriber', 'notifier'
GO

--exec sp_helprotect NULL, 'sql_dependency_subscriber'

-- query currently subscriptions
--SELECT * FROM sys.dm_qn_subscriptions
--KILL QUERY NOTIFICATION SUBSCRIPTION 42

--select * from sys.internal_tables

-- query the undelivered subscripted notification
--SELECT * FROM sys.transmission_queue