Sql-server – Schemas and user rights

sql serversql-server-2005sql-server-2008

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".

Best Answer

You need to look into using certificates to sign the stored procedures. This will allow users to access anothers tables.

Related Question