Edit

Share via

Column-level security in Fabric data warehousing

  • Article

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

Column-level security simplifies the design and coding of security in your application, allowing you to restrict column access to protect sensitive data. For example, ensuring that specific users can access only certain columns of a table pertinent to their department.

Column-level security at the data level

The access restriction logic is located in the database tier, not in any single application tier. The database applies the access restrictions every time data access is attempted, from any application or reporting platform including Power BI. This restriction makes your security more reliable and robust by reducing the surface area of your overall security system.

Column-level security only applies to queries on a Warehouse or SQL analytics endpoint in Fabric. Power BI queries on a warehouse in Direct Lake mode will fall back to Direct Query mode to abide by column-level security.

Restrict access to certain columns to certain users

In addition, column-level security is simpler and than designing additional views to filter out columns for imposing access restrictions on the users.

Implement column-level security with the GRANT T-SQL statement. For simplicity of management, assigning permissions to roles is preferred to using individuals.

Column-level security is applied to shared warehouse or lakehouse accessed through a SQL analytics endpoint, because the underlying data source hasn't changed.

Only Microsoft Entra authentication is supported. For more information, see Microsoft Entra authentication as an alternative to SQL authentication in Microsoft Fabric.

Examples

This example will create a table and will limit the columns that charlie@contoso.com can see in the customers table.

SQL 
CREATE TABLE dbo.Customers
  (CustomerID int,
   FirstName varchar(100) NULL,
   CreditCard char(16) NOT NULL,
   LastName varchar(100) NOT NULL,
   Phone varchar(12) NULL,
   Email varchar(100) NULL);

We will allow Charlie to only access the columns related to the customer, but not the sensitive CreditCard column:

SQL 
GRANT SELECT ON Customers(CustomerID, FirstName, LastName, Phone, Email) TO [Charlie@contoso.com];

Queries executed as charlie@contoso.com will fail if they include the CreditCard column:

SQL 
SELECT * FROM Customers;
Output 
Msg 230, Level 14, State 1, Line 12
The SELECT permission was denied on the column 'CreditCard' of the object 'Customers', database 'ContosoSales', schema 'dbo'.

Next step

Implement column-level security in Fabric Data Warehousing

Related content