Microsoft SQL Server permission chaining

Josef RichbergJosef Richberg
1 min read

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.

0
Subscribe to my newsletter

Read articles from Josef Richberg directly inside your inbox. Subscribe to the newsletter, and don't miss out.

Written by

Josef Richberg
Josef Richberg

I've been working in and around SQL for over 3 decades. I am currently working on making our business intelligence systems self-driven by using an event-based architecture. This involves providing curated data in some of the largest SQL Azure Analysis Services cubes ever built (over 6 billion records in 1 table alone). I've developed several APIs leveraging a serverless Azure architecture to provide real-time data to various internal processes and projects. Currently working on Project Dormouse; Durable select messaging queue that enables polling processes to extract specific messages.