Microsoft SQL Server permission chaining

Many times, views are used as a security object; Granting select on a given view instead of the underlying table(s). Now if this view happens to cross schemas you might get an error saying the user does not have select permission on the underlying table(s). As an example, a user might be restricted to the customer schema but need to view information from the inventory schema.

One might naturally simply give select permission to that specific table in the inventory schema. If you did this, you reduce the security aspect of hiding the underlying schema/objects. The correct method is simply to make sure both schemas are ‘owned’ by the same user. We have everything owned by dbo, so you need to run this on all schemas involved.

Instead run this command on both the inventory and customer schema:

alter authorization on schema::[inventory] to dbo;
alter authorization on schema::[customer] to dbo;

You preserve the integrity of your security model, while being able to properly isolate data via schemas.