I will get this to work! Currently I have to be logged in as an administrator to get SqlDependency to work. I have tried giving a variety of notification related permissions in the database to my lowly user login, but to no avail.
I just came across this – oh, what a shocking place to find the info in the documentation for VS2005! 🙂 I must have come across it early on before I understood what the heck I was reading. Now it makes sense to me though and these looks like some good clues!
Security
The dependency infrastructure includes a client-side listener that is contacted by the server to send notifications. This listener is protected by Code Access Security attributes and by authentication. For more information, see SqlNotificationPermission Class and SqlNotificationAuthType Enumeration.
Code Access Security Assertions
The listener infrastructure enables the listener to listen on TCP ports or HTTP ports depending on which system is running. To use the listener, which is necessary to use SqlDependency, the caller must have SqlNotificationPermission. The use of the SqlNotification request is independent of the dependencies and listener infrastructure on the client. Therefore listener permission is not required.
I know that I did grant Query Notification permissions to my lowly user account ( as a test) and also QueryNotifcationErrorsQueue as the exception instructed me to. I also opened up some of the recommended array of ports (which at the time was supposed to be a bit of a hack fix).
Still I never got it working.But I am definitely adamant about succeeding at this! So I will be sure to post my solution when I get there.
www.acehaid.org
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!