Trouble Implementing Column-Level Security in Microsoft Fabric for Specific User

[HamidBee]

Hi All,

Just a note bare in mind that me and the other user are currently using a trial license. I'm experiencing difficulties implementing column-level security for a table in Microsoft Fabric's data warehousing solution. I need to restrict a user's access to certain columns of a table, but despite following the documentation and applying the necessary T-SQL GRANT statements, the user still has access to all columns.

Here's what I've done:

1. Verified the user exists in the database with EXTERNAL_USER status.

2. Used the following T-SQL command to grant selective access to the Dim_Customer
   table within the Sales schema, explicitly excluding the EmailAddress column:

GRANT SELECT ON Sales.Dim_Customer (CustomerID, CustomerName) TO [xxx@domain.com]; 

3. I then shared the Data Warehouse with the user. However, when the user runs a select query on the Sales.Dim_Customer table, they can view all columns, including the ones they should not have access to. I've confirmed that the correct permissions were applied using the sys.fn_my_permissions function, and it shows that the SELECT permission is granted only on the intended columns.

What might be the cause of this issue where the user can still access all data? Could there be an override at a higher level that I'm not aware of, or is there a specific consideration in Microsoft Fabric that I'm missing?

I would appreciate any guidance on how to properly implement column-level security in this environment.

Thanks in advance.

[AndyDDC]

Hi @HamidBee when you shared the Warehouse, what level of sharing did you give the user?

"Default permissions (no additional options selected)" should allow you to share the warehouse with Connect only permissions, then use GRANT to assign the relevant permissions 

https://blog.fabric.microsoft.com/en-us/blog/data-warehouse-sharing/

[HamidBee · Accepted solution]

It turns out it was the format of the T-SQL statement I should have written it like this:

GRANT SELECT (CustomerID, CustomerName) ON Sales.Dim_Customer 
TO [xxx@domain.com];

The other format (althought it executes correctly gives a warning):

This was incredibly frustraing because this was the format Microsoft suggested in their guide:

https://learn.microsoft.com/en-us/fabric/data-warehouse/tutorial-column-level-security