topic Re: sql analytical endpoint cls in Data Engineering

sql analytical endpoint cls

Navsharma — Mon, 15 Jul 2024 16:48:04 GMT

Hi Team,

I am trying to implement the column level security to lakehouse tables using the SQL endpoint. I am using the command mentioned in microsoft learn as

GRANT SELECT ON YourSchema.YourTable 
(Column1, Column2, Column3, Column4, Column5) 
TO [User01];

User01 has 'member' permission at the workspace level. To test the functionality, we are trying to access the columns that user doesn't have access through notebooks and sql endpoint but user01 is still able to access all the columns. Any idea what is wrong with this approach?

Thanks!

Re: sql analytical endpoint cls

Anonymous — Mon, 15 Jul 2024 05:19:28 GMT

Hi @Navsharma ,

When the permission of workspace is Admin, Member, or Contributor, it will override the permission of column level security. This provides them access to all Items within the workspace.

If they primarily require read only access, assign them to the Viewer role and grant read access on specific objects through T-SQL. For more information, see Manage SQL granular permissions.

Other users, who only need access to an individual warehouse or require access to only specific SQL objects, should be given Fabric Item permissions and granted access through SQL to the specific objects.
You can manage permissions on Microsoft Entra ID (formerly Azure Active Directory) groups, as well, rather than adding each specific member.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: sql analytical endpoint cls

Navsharma — Mon, 15 Jul 2024 16:34:12 GMT

@Anonymous : That make sense. Also another thing when we are setting up the CLS at lakehouse delta tables, will it work if I use direct lake connection for PBI reports or user has to use the import mode ?

Re: sql analytical endpoint cls

Anonymous — Mon, 29 Jul 2024 07:46:45 GMT

Hi @Navsharma ,

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.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.