How to find missing permission in MS SQL server

1

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

sql-server
asked on Server Fault Sep 4, 2019 by John Ranger

2 Answers

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

answered on Server Fault Sep 4, 2019 by LTPCGO
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>
answered on Server Fault Sep 6, 2019 by John Ranger

User contributions licensed under CC BY-SA 3.0