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.

You could probably just do this very easy with remoting over an IPC channel. — leppie
– leppie, Commented Feb 23, 2010 at 15:58
Why do users need CREATE permissions? I thought they just needed SqlClientPermission.Unrestricted and the SUBSCRIBE QUERY NOTIFICATIONS database permission. (Per msdn.microsoft.com/en-us/library/ms172133.aspx) Are you proposing allowing users to enable query notifications on arbitrary servers? (Since the setup code does require some alter and create statements.) — Jeff Sternal
– Jeff Sternal, Commented Feb 23, 2010 at 16:10

Remus Rusanu · Accepted Answer · 2010-02-23 21:10:59Z

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.

Jamie Altizer · Accepted Answer · 2010-02-23 17:30:02Z

0

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.

answered Feb 23, 2010 at 17:30

Jamie Altizer

1,5421 gold badge12 silver badges17 bronze badges

Collectives™ on Stack Overflow

Replacing SqlDependency with manual code

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related