Microsoft SQL Server permission chaining

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.

I have over 34 years in the world of RDBMS. I have in-depth knowledge of Microsoft SQL Server and the Azure platform (Data Factories, Logic Apps, Functions, Event Grids).

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.

Microsoft SQL Server permission chaining

Comments

More from this blog

Analysis Services models in Azure Fabric -- METADATA$ROW_ID gotcha

Permissions required for user defined table types in Microsoft SQL Server

Azure Data Factory utcNow() nuances

Using a stored procedure as a source for PowerBI Paginated reports

Command Palette

Comments

More from this blog