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

[v-prasare]

Hi @uk-roberto92,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@GilbertQ & @Olufemi7 ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[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 · Accepted solution]

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.