how to share one table in data warehouse

[Jeanxyz]

Instead of giving a user read access to the whole workspace, I want to share one table with a user. 

I have created the table and column level security via T-SQL scripte below. I have share the data warehouse with the user with read only permission, how can the user find this table without direct access to the workspace or warehouse ?

CREATE TABLE dbo.Patients

(  

    PatientID INT NOT NULL,  

    FirstName varchar(50) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,    

    LastName varchar(50) NOT NULL,    

    Phone varchar(20) MASKED WITH (FUNCTION = 'default()') NULL,    

    Email varchar(50) MASKED WITH (FUNCTION = 'email()') NULL  

);

INSERT dbo.Patients (PatientID, FirstName, LastName, Phone, Email) VALUES

(29485,'Catherine','Abel','555-555-5555','catherine0@adventure-works.com'),

(29486,'Kim','Abercrombie','444-444-4444','kim2@adventure-works.com'),

(29489,'Frances','Adams','333-333-3333','frances0@adventure-works.com');

SELECT * FROM dbo.Patients;

CREATE ROLE Doctor AUTHORIZATION dbo;

CREATE ROLE Nurse AUTHORIZATION dbo;

CREATE ROLE Receptionist AUTHORIZATION dbo;

CREATE ROLE Patient AUTHORIZATION dbo;

GO

-- Grant SELECT on all columns to all roles

GRANT SELECT ON dbo.Patients TO Receptionist;

GO

-- Deny SELECT on the Email column to the Receptionist 

DENY SELECT ON dbo.Patients (Email) TO Receptionist;

ALTER ROLE Receptionist ADD MEMBER [cc@xxx];

Go

[libpekin]

@Jeanxyz what's the use case for wanting to share a single table in a fabric warehouse?
One approach is to import the table into Power BI by importing the Parquet table files. Recall that the Fabric warehouse, similar to the lakehouse, is built on data lake architecture.

To import the Parquet table files, follow these steps:

1. Open Power BI Desktop.

2. Select "Get Data" and choose "Parquet" from the list of data sources.

3. Navigate to the location of your Parquet files and select the file you want to import.

4. Load the data into Power BI and start building your visuals.

[Jeanxyz · Accepted solution]

Here is my findings after some tests:

1. to share a table with a user, I need to share the whole warehouse with the user with "read" only permission, however, this will cause a problem when user connect to the database via Power BI desktop.

2. Due to read permission, user can access all the tables in the warehouse via PB Desktop, also user won't be able to read data from the table with Column Level Security because by default PB Desktop will select all columns, which will cause an error due to CLS setup. 

So to conclude, the whole security solution suggested by Microsoft is flawed. For the moment, the best way is to share the whole warehouse with the user. Sharing a single table with a user requires additional security setup in Fabric and need thorough test before put into implementation. 

[Anonymous]

Hi @Jeanxyz ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

Regards,

Xiaoxin Sheng

[FabianSchut]

Hi, in what way do you want the user to "find" the table? You can share the SQL-endpoint string with the user and the user can query that table with any tool/in any application of preference, for example in Azure Data Studio. However, the user will not be able to see the table in the Fabric UI.