To enable the Object Change Listener, follow these steps:
To enable and assign minimum permissions for the Object Change Listener
1. Open SQL Server Management Studio. and then connect to your SQL Server instance.
2. On the File menu, point to New, and then click Query with Current Connection.
3. Type the following SQL statements.
Copy
USE MASTER
CREATE LOGIN [ReplaceWithNAVServerAccount] FROM WINDOWS;
GO
4. Highlight the lines that you typed and, on the Query menu, click Execute.
5. Now type these lines below the existing lines.
Copy
USE [ReplaceWithYourDatabaseName]
CREATE USER [ReplaceWithNAVServerAccount] FOR LOGIN [ReplaceWithNAVServerAccount];
6. Highlight the lines that you just typed and, on the Query menu, click Execute.
7. Now type these lines below the existing lines.
Copy
CREATE SCHEMA [$ndo$navlistener] AUTHORIZATION [ReplaceWithNAVServerAccount];
GO
8. Highlight the lines that you just typed and, on the Query menu, click Execute.
It's very possible that you will see an error stating that the schema in question already exists. This is not a problem.
9. Now type these lines below the existing lines.
Copy
ALTER USER [ReplaceWithNAVServerAccount] WITH DEFAULT_SCHEMA = [$ndo$navlistener];
GRANT SELECT ON [Object Tracking] TO [ReplaceWithNAVServerAccount];
GO
10. Highlight the lines that you just typed and, on the Query menu, click Execute.
|
Note |
|
The Object Tracking table name may be in a different language than English. If it is, then replace "Object Tracking" with the actual table name from your database. |
11. On the Query menu, click Execute.
Enabling Query Notification for the Object Change Listener
Enabling query notifications is not required but can improve performance when the Object Change Listener is listening for changes.
To enable query notification for the Object Change Listener
1. Open SQL Server Management Studio.
2. On the File menu, point to New, and then click Query with Current Connection.
3. Type the following SQL statements:
Copy
ALTER DATABASE [ReplaceWithYourDatabaseName] SET ENABLE_BROKER;
GRANT CREATE PROCEDURE TO [ReplaceWithNAVSrvrAcct];
GRANT CREATE QUEUE TO [ReplaceWithNAVSrvrAcct];
GRANT CREATE SERVICE TO [ReplaceWithNAVSrvrAcct];
GRANT VIEW DEFINITION TO [ReplaceWithNAVSrvrAcct];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [ReplaceWithNAVSrvrAcct];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [ReplaceWithNAVSrvrAcct];
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [ReplaceWithNAVSrvrAcct];
GO
4. On the Query menu, click Execute.
MSDN Official Issue
http://msdn.microsoft.com/en-us/library/dd568739.aspx