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;
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.