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