0

I've been trying to find information on this for quite some time but I couldn't find any confirmed results. I know that we can grant access to view without granting explicit access to the base table on MS SQL Server but I'm not able to do so in Azure Synapse Data Warehouse.

I'm not sure if I'm doing it wrong or it's just that there is a different way of doing it. I couldn't get any relevant information on this, can someone please verify this?

I'll elaborate more since it seems not clear what I'm trying to achieve;

We have base table; [schemaA].[OverallTeamSales]

We have a team that will consume a subset of [schemaA].[OverallTeamSales], thus we created a View with applied filter;

CREATE VIEW [schemaB].[ABCDTeamSales] AS Select * from [schemaA].[OverallTeamSales] WHERE [Team] = 'abcd'

Then grant the user SELECT access to [schemaB].[ABCDTeamSales]

GRANT SELECT ON [schemaB].[ABCDTeamSales] to [ABCD_ReadRole]

This doesn't seems to work as user was prompted with error below

The SELECT permission was denied on the object '[schemaA].[OverallTeamSales]'

I believe this would have work on MS SQL but doing the same on Azure Synapse Data Warehouse doesn't seems to work and I couldn't find any information related to this

Do let me know if this is still not clear.

Improve this question
2
  • Why are you "not able to do so in Azure Synapse"? What have you tried, what was the outcome, and what were your expectations?
    – mustaccio
    Commented Feb 15, 2022 at 2:33
  • @mustaccio, I've elaborate the descriptions in detail. Hope that clears that up. Thank you!
    – zuheir
    Commented Feb 15, 2022 at 2:46

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.