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,