*Words of Wisdom From The Elder

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.