Once for all right permissions for SQLDependency PLEASE?

Question

I took this description out of my search engine because it is exactly what is happening to us, so why bother writing it again.

The original post is unanswered here (http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic30682.aspx)

 but I hope that it will be solved here.

The blog entry posted by Sushil Chordia at
http://blogs.msdn.com/dataaccess/archive/2005/09/27/474447.aspx describes
the minimum requirements a Sql user should have for SqlDependency to work. I
have tried to apply them, but I still have some problems.

I am trying to create an Application Role in Sql Server 2005 RTM that
contains all the necessary rights for SqlDependency to work. I'm doing this
based on the information specified in the blog mentioned earlier.

Here's the script that creates a test-databases and the application-role:
CREATE DATABASE [TestDb] ON  PRIMARY
( NAME = N'TestDb', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\TestDb.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestDb_log', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\TestDb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB
, FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AI
GO

USE TestDb
GO

CREATE TABLE [dbo].[tblPRODUCTS](ID INT NOT NULL, strNAME NVARCHAR(50) NOT
NULL)
GO

CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[TestDb],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE USER [test] FOR LOGIN [test]
GO

EXEC sp_addrole 'sql_dependency_subscriber'
GO

CREATE ROLE [sqldependency_user]
GO

GRANT CREATE PROCEDURE to [sqldependency_user]
GRANT CREATE QUEUE to [sqldependency_user]
GRANT CREATE SERVICE to [sqldependency_user]
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification ] to [sqldependency_user]
GRANT VIEW DEFINITION TO [sqldependency_user]
GRANT SELECT to [sqldependency_user]
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sqldependency_user]
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sqldependency_user]
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification ] to [sqldependency_user]

EXEC sp_addrolemember 'sql_dependency_subscriber', 'test'
EXEC sp_addrolemember 'sqldependency_user', 'test'
GO

I have also written the following C# program that should work at this point
if I interpret that blog-entry correctly.
  class Program
  {
    private static string mConnectionString = "Data
Source=(local);Database=TestDb;Persist Security Info=false;Integrated
Security=false;User Id=test;Password=test";

    static void Main(string[] args)
    {
      using (SqlConnection oConnection = new SqlConnection(mConnectionString))
      {
        oConnection.Open();

        SqlDependency.Start(mConnectionString);

        ' there's some more code here...
      }
    }
  }


However, if I now run this program, I get the following exception on
SqlDependency.Start:
Unhandled Exception: System.Data.SqlClient.SqlException: The specified
schema name "dbo" either does
not exist or you do not have permission to use it.

I partially resolved this problem by doing the following:
GRANT CONTROL ON SCHEMA::[dbo] TO [adam_service_user]

However, when I run the application again, it now fails with the following
error on SqlDependency.Start:
Unhandled Exception: System.Data.SqlClient.SqlException: Cannot find the
user 'owner', because it do
es not exist or you do not have permission.
Cannot find the queue
'SqlQueryNotificationService-653e2c6f-51ff-488e-bca6-71be01a02206', because it
does not exist or you do not have permission.
Cannot find the service
'SqlQueryNotificationService-653e2c6f-51ff-488e-bca6-71be01a02206', because
it does not exist or you do not have permission.
Invalid object name
'SqlQueryNotificationService-653e2c6f-51ff-488e-bca6-71be01a02206'.

Apparently, there's still some additional permission required to use
SqlDependency that is not listed in Sushil's blog. I can bypass this problem
by making my role a member of the dbo-role, but I would like to know the
minimum role required to make this thing work.

Answer 1

Here's a blog posting I wrote that addresses your specific issue. http://www.sqlskills.com/BLOGS/BOBB/post/About-SCHEMAs-and-setting-up-Query-Notifications.aspx. Let me know if you have any further questions.

Cheers,
Bob Beauchemin
SQLskills

Answer 2

We went through your article and we still can't get it right.
Now we get on the start SqlDependency.Start the following exception:
Cannot find the specified user 'owner'.
Cannot find the queue 'SqlQueryNotificationService-2c7eb664-a9bd-46a5-8003-bb88248e851a', because it does not exist or you do not have permission.
Invalid object name 'SqlQueryNotificationService-2c7eb664-a9bd-46a5-8003-bb88248e851a'.

We have the Application pool user register with SQL server and everything.

Thanks for your help!

Answer 3

I think we did what Bob recomends in his article, what are we missing?

CREATE ROLE [SQLDependency] AUTHORIZATION [dbo]
GO
CREATE SCHEMA [SQLDependency] AUTHORIZATION [SQLDependency]

EXEC sp_addrolemember N'SQLDependency', N'OurUser'
GO
ALTER USER [OurUser] WITH DEFAULT_SCHEMA=[SQLDependency]
GO

From Bob's post:
A better alternative might be to create a database role for this function and create the default schema owned by the role. Then add startUser to the role. You still have to alter the user's default_schema in this case, because database roles cannot have default schemas. Roles cannot have default_schemas themselves because if one user was a member of 3 different roles and each role had a different default_schemas which one would "win"?

Answer 4

You might run a SQL Profiler trace to see what's getting created and where
the query notification is looking. You can also do a "select * from
sys.service_queues" to see where the queue is created (you're looking for
schema_id)

Cheers,
Bob Beauchemin
SQLskills