Problem sourcing data in Fabric, not in SQL

[uk-roberto92]

Hi,

I am trying to source some data from a SQL table in Fabric service.

I can access the table via SQL SSML, but not via Fabric, with the same user, getting the following error:

The SELECT permission was denied on the object...

I have a Fabric gateway to database xxxx in server S1, bu trying to source this data from a third database yyyyy  in same server, through the advanced query option in Fabric (select * from yyyy.dbo.table).

It seems some access issue, but again I can access this table with the same user via SSML.

Any help?

Thanks

[GilbertQ]

Hi @uk-roberto92 

Another recommendation I would highly recommend is to use SQL Profiler on your database so you can understand what user is trying to get in to your database and grant the associated permissions as listed above.

[Olufemi7]

Hello @uk-roberto92,

Fabric queries run in the context of the gateway database, so even if your user works in SSMS, it needs explicit permissions in the target database. Grant SELECT on the table (or add the user to db_datareader) in yyyyy, then fully qualify the table in Fabric (yyyyy.dbo.table). This resolves the permission error.