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.
Something that may be the cause of the issue is that he has the Synapse SQL administrator in the roles, we feel this might override any restrictions on his permissions in the database. However, if we were to remove the SQL administrator role and give him something else like Synapse Contributor, he loses the ability to even see the SQL database.
It seems to suggest he does not have access to the SQL pool, but I see no option to give permissions in the SQL built in pool.
Please see some of the code we used below (It ran fine)
USE database;
Create Role "Basic Access";
Alter Role "Basic Access" Add Member [john doe] ;
GRANT VIEW DATABASE STATE TO [[email protected]];
GRANT CONNECT TO [[email protected]];
GRANT SELECT ON OBJECT::dbo.tbl_a TO [[email protected]];
Any guidance would be greatly appreciated!
4