locked
Once for all right permissions for SQLDependency PLEASE? RRS feed

  • 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.
    Friday, October 30, 2009 3:53 PM

Answers

All replies

  • 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
    Friday, October 30, 2009 5:05 PM
  • 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!

    Monday, November 2, 2009 4:16 PM
  • 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"?

    Monday, November 2, 2009 4:36 PM
  • 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

    Monday, November 2, 2009 5:53 PM
  • I have similar error message as yours that said Cannot find the user 'owner' and able to solve the problem by changing the ownership of the [SqlDependency] schema to the user [adam_service_user] instead of the ROLE [SqlDependency].

    eg.

    change the CREATE SCHEMA [SQLDependency] AUTHORIZATION [SQLDependency] to

    CREATE SCHEMA [SQLDependency] AUTHORIZATION [adam_service_user]

    Good luck.
    • Proposed as answer by Frederick Teng Tuesday, December 1, 2009 11:04 AM
    Tuesday, December 1, 2009 11:03 AM
  • Bob,

    I have the same problem as people in this thread. Tried all suggestions - still does not work - I get the error:

    Cannot find the user owner, because it does not exist, etc.

    SQL dependency tries to create a Queue:
    SqlQueryNotificationService-..., where... seems to be a quid, which changes every time you try to initiate new dependency.

    Following your suggestion - looked up the schema which owns above queue - it is the 'dbo' schema, id = 1. Making dbo a securable and granting permissions onto dbo makes no difference. 

    Has anybody solved this problem?? I see from the dates the thread is three years old... Has anybody looked at this since then?

    Thanks in advance.

    Peter Battelino
    BTE Corporation


    PMBT

    Saturday, June 23, 2012 5:40 PM
  • me too! (make that 4 years)
    Tuesday, January 8, 2013 7:59 PM
  • Since this is unanswered here for 4 years, and still comes up in searches for the error message without a proper solution:

    The database has a schema for users of SqlDependency which was created like so:

    CREATE SCHEMA [sql_dependency_user]
    GO

    Creating the user:

    CREATE USER [myUser] FOR LOGIN [myDomain\myUser] WITH DEFAULT_SCHEMA=[sql_dependency_user]
    GO

    Doing SQLDependency.Start() with the connection string "Data Source=<my server's name>;Initial Catalog=<my database>;Integrated Security=true" when logged in as Windows user <myUser> resulted in the error:

    "CREATE PROCEDURE permission denied in database 'MyDatabase'"

    So:

    GRANT CREATE PROCEDURE TO [myUser]

    then the error becomes:

    "The specified schema name "sql_dependency_user" either does not exist or you do not have permission to use it."

    So: GRANT CONTROL ON SCHEMA::[sql_dependency_user] TO [myUser]


    then error: "CREATE QUEUE permission denied in database 'MyDatabase'."

    Command: GRANT CREATE QUEUE TO [myUser]

    then error: "Cannot find the user 'owner', because it does not exist or you do not have permission.
    CREATE SERVICE permission denied in database 'MyDatabase'."

    Command: GRANT CREATE SERVICE TO [myUser]

    Then the message becomes:

    "Cannot find the user 'owner', because it does not exist or you do not have permission.
    Cannot find the queue 'SqlQueryNotificationService-<guid>', because it does not exist or you do not have permission.
    Invalid object name 'SqlQueryNotificationService-<guid>'."

    This goes away with

    GRANT IMPERSONATE ON USER::DBO TO [myUser]

    Then the error is:

    "Cannot find the contract 'http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification', because it does not exist or you do not have permission.
    Invalid object name 'SqlQueryNotificationService-<guid>'."

    So let's allow referencing the contract:

    GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [myUser];

    This is everything needed to start an SQL dependency.

    If, in addition, you also want to subscribe to the notifications, like so (in C#):

    using (var sqlConnection = new SqlConnection(connectionString))
    {
        var sqlCommand = new SqlCommand( <query over my table>);
        sqlDependency = new SqlDependency(sqlCommand);
        sqlConnection.Open();
        sqlCommand.ExecuteReader();
    }

    ...the error message will be "User "myUser" does not have permission to request query notification subscriptions on database "MyDatabase"."
    You also need:

    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [myUser];

    And then, impersonating dbo apparently does not solve the permissions entirely, because you may get the following:
    "The SELECT permission was denied on the object '<my table>', database 'MyDatabase', schema 'dbo'."

    this is finally solved by
    GRANT SELECT ON SCHEMA::[dbo] TO [myUser]

    And voila! Now SQLDependency works.

    Again, everything that was required:

    ===========================================

    To start the SQL dependency:
    CREATE USER [myUser] FOR LOGIN [myDomain\myUser]
    WITH DEFAULT_SCHEMA=[sql_dependency_user]
    GO
    GRANT CREATE PROCEDURE TO [myUser]
    GRANT CONTROL ON SCHEMA::[sql_dependency_user] TO [myUser]

    GRANT CREATE SERVICE TO [myUser]
    GRANT CREATE QUEUE TO [myUser]
    GRANT IMPERSONATE ON USER::DBO TO [myUser]
    GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [myUser]

    To subscribe to the SQL dependency:
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [myUser];
    GRANT SELECT ON SCHEMA::[dbo] TO [myUser]

    ============================================

    The one thing that made the "Cannot find the user 'owner'" go away was the GRANT IMPERSONATE ON USER::DBO TO [myUser] .

    Btw, I tested this with SQL Server 2012, which apparently still has the same error messages.

    HTH someone.

    Wednesday, November 6, 2013 5:13 AM