I’m working on a project in Synapse and need to assign roles to users such that certain people can see specific data while others cannot. Here’s what I’ve tried so far:
Creating a role using the CREATE ROLE “Basic Access” command in SQL script, which gives the user access to one of 30 views. We then assigned a user to the role in order to test it, the user could unfortunately still access everything. Although when he used Execute as [his user] at the start it works, however this is not what we want going forward. We would like the SQL database (serverless) to recognise the user and enforce restrictions straight away.