I went around and around (and dragged poor Sushil Chordia and blogless Leonid Tsybert along for the ride) with an access permission problem that my ASPNET account was having when trying to do SqlDependency.Start. All of the correct permissions (listed below) had been applied to the account, which could be verified with sp_helprotect,NULL,ASPNET
Finally, I just gave up and removed the ASPNET account from SQL Server in its entirety and recreated it with the necessary permissions and everything worked just fine.
Though I’m very frustrated not to have figured out what was causing the problem, I’m satisfied in knowing that there is a solution (remove & recreate.)
Here, as listed in Sushil’s fantastic blog post from late September, which listed all of the RTM changes for Query Notification, is how to set up the perms.Note that this is for IIS5 whereas in IIS6 you would use the NT Authority\NetworkService account.
All of this is run against the database that you want the perms for, not in Master.
sp_grantlogin ‘myMachineName\ASPNET’ –this gives the ASPNET account login access to SQL Server
sp_grantdbaccess ‘MyMachineName\ASPNET’, ASPNET –this gives the login access to the database with the “nickname” ASPNET
GRANT CREATE PROCEDURE to ASPNET
GRANT CREATE QUEUE to ASPNET
GRANT CREATE SERVICE to ASPNET
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to ASPNET –note that the schema is case sensitive!
GRANT VIEW DEFINITION to ASPNET
The above are to call Start(), to do the actual notifications, you need:
EXEC sp_addrole ‘sql_dependency_subscriber’
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO ASPNET
GRANT RECEIVE ON QueryNotificationErrorsQueue TO ASPNET
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to ASPNET
EXEC sp_addrolemember ‘sql_dependency_subscriber’, ‘ASPNET’
GRANT SELECT TO ASPNET
Don’t Forget: www.acehaid.org
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!