Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 schemas, lets say sch1 and sch2. Each owned by different user.

I do want to grant SELECT rights on sch2.MyTable (but just this one object) to sch1 procedures/views AND allow to reference sch2.MyTable in sch1 foreign key constraints.

I do NOT want to make sch2.MyTable directly accessible to those, who uses sch2 procedures/views, but want to allow indirect access.

Basically sch2 is much more important. Owners of sch2 have control of sch1 as well, but not vice a versa. On the other hand sch1 has some data that is dependant on sch2 and they should be able to see them and should not allow delete data from sch2, while sch1 still has some dependencies on them.

Is that possible (maybe through synonyms)?

For no it seems- no, not possible. Unless take off part about "Views and Constraints" OR "different schema owners".

so- is should take off part about "Views and Constraints". Views cannot be signed, and constraints will not be possible to make if schema sch1 users are not able to access that table from sch2.
–
JānisAug 10 '12 at 8:19

I believe that the DBA (or member of the db_owner fixed database role) would need to create the constraints. The same probably applies to views.
–
mrdenny♦Aug 10 '12 at 8:22

wow, that really works! is sch1 and sch2 are owned and managed by different users and none of them have permissions to other schema tables, db_owner still can make constraints from one schema table to other schema table.
–
JānisAug 10 '12 at 8:51

You should be able to do this easily by just granting EXEC on the stored procedure(s) and SELECT on the view(s) to the user(s) you want to indirectly access the table. We do this in our office all the time, we don't grant permissions to underlying tables and only allow access to the underlying data through views/procedures.

If i give "Select" permission on table, the user is able to select data from table directly. Ownership chaining would solve this, but that does not work as well because schemas are owned by different users.
–
JānisAug 10 '12 at 7:24