Today I spent the better part of the day working with one our DBAs at work to setup SqlDependency so it would work with SQL Server. In order to use SqlDependency it requires Service Broker. Thus we enabled Service Broker on the database. The rest of the day we kept getting the following error each time SqlDependency would get called on start up.
Error: Message: Cannot find the procedure ‘SqlQueryNotificationStoredProcedure-42d557f5-071f-456d-8d4c-a1bafc4cbece’, because it does not exist or you do not have permission.
Cannot find the queue ‘SqlQueryNotificationService-42d557f5-071f-456d-8d4c-a1bafc4cbece’, because it does not exist or you do not have permission.
Invalid object name ‘SqlQueryNotificationService-42d557f5-071f-456d-8d4c-a1bafc4cbece’.
After much Kung Fu searching and trial and error we finally found something. We had to enable the following permissions to get this to work. Thanks to Remus Rusanu for the information.
A security red flag with these permissions is the “GRANT CONTROL ON SCHEMA::[dbo] to [SqlUser];”. This opens up the permissions extensively on the database for that user to do all sorts of things. I’m not a DBA, but reading the documentation on what GRANT CONTROL scared me enough to know we can’t leave this in a production environment. Jim, the DBA at work, has an idea to tune this down.
Jim, our Kung Fu DBA found an article that outlined some finer grained permissions here. The script on that page assumes you have two different users or services. One that creates the dependency, one that is the listener. Jim’s script assumes you have only one account doing both functions. This is how most setups will be more than likely. Thus use this script instead of the one above, unless you like security holes in your database.
Thanks to Jim the Kung Fu DBA for finding a better solution.