Featured Post

Detroit Launch Event, It wasn’t my fault, Let Me Explain

This past week I have been in Michigan.  My reason for traveling to Michigan this week is I spoke at the MSDN Launch event in Detroit and used the time while I was up to work onsite this week as well. This is the first chance I’ve had all week to sit down at the computer and just vegetate.  It...

Read More


SqlDependency and SQL Service Broker Permissions

Posted by Keith Elder | Posted in .Net, SQL Server | Posted on 20-01-2009

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. 

GRANT CREATE PROCEDURE TO [SqlUser];
GRANT CREATE SERVICE TO [SqlUser];
GRANT CREATE QUEUE TO [SqlUser];
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [SqlUser];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [SqlUser];
GRANT CONTROL
ON SCHEMA::[dbo] TO [SqlUser];
GRANT IMPERSONATE ON USER::DBO TO [SqlUser];

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. 

Better Permissions

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. :)

--Script to give user:[User] rights to use the SQLDependency functionality in .Net 2.0
CREATE ROLE [SQLDependency] AUTHORIZATION [dbo]
GO
 
CREATE SCHEMA [SQLDependency] AUTHORIZATION [SQLDependency]
GO
 
--Database level permissions
GRANT CREATE PROCEDURE to [SQLDependency];
GRANT CREATE QUEUE to [SQLDependency];
GRANT CREATE SERVICE to [SQLDependency];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [SQLDependency];
GRANT VIEW DEFINITION TO [SQLDependency];
 
--Service Broker permissions
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [SQLDependency];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [SQLDependency];
GO
 
EXEC sp_addrolemember N'SQLDependency', N'[User]'
GO
 
ALTER USER [User] WITH DEFAULT_SCHEMA=[SQLDependency]
GO

Thanks to Jim the Kung Fu DBA for finding a better solution.

  • Alex

    Thanks for the post.

    But by following these instructions directly I’m getting the following error:

    Cannot find the specified user ‘owner’.Cannot find the queue ‘SqlQueryNotificationService-725767c7-9532-451e-a615-b2a9a20bd800′, because it does not exist or you do not have permission.Invalid object name ‘SqlQueryNotificationService-725767c7-9532-451e-a615-b2a9a20bd800′.

  • http://www.geeks.ltd.uk/Services.html Anonymous

    That was an inspiring post,

    thanks my problem is solved.

    Thanks for writing about it