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,
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
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>
User contributions licensed under CC BY-SA 3.0