Replacing SqlDependency with manual code

Question

We work in .Net 3.5, and we have SqlDependency in the code, which requires the .Net user to have CREATE permissions and other permissions.
The DBAs here want to avoid giving the .Net user such vast permissions.

Is there a way to bypass SqlDependency by manually doing what SqlDependency does? Running a background process with more permissions that the .net one and creating the necessary procedures, etc.?

Thanks!

---

For future reference, I just want to say that we solved the permissions issue with a very simple solution: The SqlDependency uses a different connection string with a user that has different permissions that the regular .net connection string.

Answer 1

Why could you not use a stored procedure to handle this task? They are one way to abstracting permissions and db logic away from your client code.

If all objects (tables, stored procedures, etc) are owned by the same owner (many times dbo) then your client permissions only need access to execute the stored procedure(s) they require.

Answer 2

Use SqlNotificationRequest instead. This is the base, underlying class, on top of which SqlDependency is built. To understand how SqlDependency works, read on The Mysterious Notification.

In order to receive Query Notification you must have a valid service and queue. the service and queue cannot be shared by application instances because they will receive each-other's notification. This is why SqlDependency chooses to do a 'just-in-time' deployment of a temporary service, queue and procedure (when you call Start()) and that is what drives the requirement to have CREATE permissions.

The lower level SqlNotificationRequest does allow you to specify the service (and hence the queue) to be used, but the draw back is that the service and queue must already exist. However, you can create them during instalation phase when the setup or .msi is run by a priviledged administrator. When invoked by users the code uses the already created service. But again, you have to create a service for each application instance (ie. for each appdomain), which makes things a tad complicated.