SQLDependency Caching not working

Question

I am trying to use SQLDependency Caching with Query Notifications in my ASP.NET application. I followed these steps to set up SQLDependency Caching.I was able to set up the db successfully. However,when I run my application I am getting the following error.

Cannot find the specified user 'owner'.
Cannot find the queue
SqlQueryNotificationService-6c3ae823-a678-4ed2-8b97-561b5f2561ee', 
because it does not exist or you do not have permission.
Invalid object name
SqlQueryNotificationService-6c3ae823-a678-4ed2-8b97-561b5f2561ee'.

Code Global.asax

void Application_Start(object sender, EventArgs e)
    {
        // Code that runs on application startup
        System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["McdConn"].ToString());

    }

I tried to Google the solution but I could not get any help.

Can anyone help me with this?

Am I missing something?

Answer 1

Either you can give the User sysadmin rights OR

provide alter permission to the dbo schema.

In order to create a procedure, a function, schema scoped objects etc.., you need at least ALTER permission on the target schema. Here is the TSQL syntax,

grant alter on schema :: schema_name to  user_name

The user should also have the permissions on the Notification Service , Queues. To grant the permission to user ‘John’ on the Service and queue use:

GRANT SEND on service::SqlQueryNotificationService to John
GRAND RECEIVE on SqlQueryNotificationService_DefaultQueue to John

Also the user needs permission to subscribe to notification.( This is done already by you I guess) To do this use:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO John

One last point so far i can guess to help is that in case you have setup SqlDependency on a command, then make sure you execute it.Query subscriptions on the server happen only on command execution.

SqlDataReader reader = command.ExecuteReader();

This post may prove more helpful.