2

Problem description: I am trying to use SQLDependency on a table of a commercial product (TAC Reservation Assistant). The DB is a large Microsoft SQL 2016 database on which we don't have db_owner rights (only TAC does).

I am now trying - together with support staff of TAC - to grant to a SQL-internal user the necessary rights to activate SQLDependency on this commercial database without granting our SQL user db_owner rights.

(With db_owner rights, this works perfectly without error - so our code is correct).

What we already tried: We so far have followed the valuable information on this site: http://keithelder.net/2009/01/20/sqldependency-and-sql-service-broker-permissions/ but because the tables which we want to observe with SQLDependency is in its own schema (tac instead of dbo), there is a permission missing on the schema because we get the following error in our c-sharp code:

Error message: Unhandled Exception occured while starting the WatcherService of Type Checkin. System.Data.SqlClient.SqlException (0x80131904): The specified schema name "tac" either does not exist or you do not have permission to use it.

Goal: As the above error message does not show us what exact right is missing, I would like to have a hint if there is somewhere a log within MS SQL server which exactly states what kind of right we don't have.

Does something like this exists?

With kind regards,

John

3
  • From your linked-to article, there is a grant on the dbo schema. Did you create the underlying queue in the tac schema? If so, that permission probably needs to be granted to that schema rather than dbo. I'm purely speculating here (as opposed to being passive aggressive, by the way).
    – Ben Thul
    Sep 5, 2019 at 0:35
  • Hmm - the queue is automatically generated when SQDependency is activated and the queue is removed when deactivating the SQLdependency. But see my answer to my own question. We finally found out all the neccessary rights needed to grant SQLdependency activation rights to a standard read-only sql user. Sep 6, 2019 at 11:49
  • Yeah, looks like I was on the right track. Glad you figured it out.
    – Ben Thul
    Sep 6, 2019 at 14:04

2 Answers 2

0
SELECT * FROM fn_my_permissions('tac.TableName', 'OBJECT')   
    ORDER BY subentity_name, permission_name ; 

This will give you permissions on a table in the tac schema. You have verified the schema definitely exists right? The result of SELECT SERVERPROPERTY('COLLATION') will also tell you whether or not case-sensitivity is turned on; ordinarily it is not, but if this returns a string containing CS then make sure the schema is 'tac' and not 'TAC' etc

3
  • If I understand this correctly, this shows the already granted rights - but not the missing rights (the ones where an access denied occur and whcih must be granted). Sep 6, 2019 at 11:54
  • Yeh, you compare it then to the possible options
    – LTPCGO
    Sep 7, 2019 at 17:28
  • The goal was to get the missing (and only the missing) permissions. In your approach (which we in our search have used), I would find out all not yet assigned permissions - but not exactly the ones which are needed (and only them). Sep 9, 2019 at 17:24
0

Many thanks for all your feedback.

although not a direct answer to my question (but the reason why I originally wrote the question) - as a reference for me and those which came here for the same problem - here the info how to grant the correct rights to a standard read-only SQL-internal user so that he/she can activate/deactivate SQLdependency on a table which has a non-standard schema.

use <database>

GRANT CREATE CONTRACT TO <user>
GRANT CREATE MESSAGE TYPE TO <user>
GRANT CREATE SERVICE TO <user>
GRANT CREATE QUEUE TO <user>
GRANT CREATE PROCEDURE to <user>
GRANT CONTROL ON SCHEMA::<schema> to <user>

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .