I recently worked on some tenant user scenarios, as a part of POCs, with customers on Azure Synapse Analytics (preview). These are more ‘what if’ kind scenarios scoped for Synapse studio (preview). It is important to understand the kind of access to be given to users so that they can work on Synapse studio.
Before we start, please note https://web.azuresynapse.net works on AAD authentication. I have five AAD users (user1 to user4) and two AAD groups ‘pocsqladmin’ and ‘pocsynapsereader; in office365 tenant. User5 is in my Org tenant.
I am owner of workspace and have created SQL Pool – ‘synapsesqlpool’ and Spark Pool –‘pocsparkpool’.
AAD group - Pocsqladmin is contributor in workspace and active directory admin of SQL Pool. It is being added as workspace, sql and spark admin from synapse studio. Also, this group has RBAC role in storage account. Additionally, I have added pocsqladmin as user in SQL Pool under security.
CREATE USER [pocsqladmin] FROM EXTERNAL PROVIDER
GRANT CONTROL ON DATABASE::synapsesqlpool TO [pocsqladmin];
User Senario1 –> User with contributor role is subscription
If user’s role in subscription = contributor then it gets inherited to synapse workspace as contributor. Following errors will be seen in synapse studio if no other permission is given.
Add user as workspace admin/SQL pool admin etc.
Or add user to pocsqladmin AAD group.
With a and b, you can create pipeline, notebook, can use mange tab etc. But this will only give select permission on objects of SQL pool. You will receive following error, if you issue ‘Create’ command
Failed to execute query.
Error: The specified schema name "firstname.lastname@example.org" either does not exist or you do not have permission to use it.
Add user explicitly to the SQL Pool under security with appropriate grant permission.
CREATE USER [email@example.com] FROM EXTERNAL PROVIDER
GRANT CONTROL ON DATABASE::synapsesqlpool TO [firstname.lastname@example.org];
User Scenario2 –> user with reader role in subscription and member of AAD group ‘pocsqladmin’
If user’s role in subscription = reader then it gets inherited to workspace as reader but as it is member of AAD group, he/she can to access entire workspace including manage tab, spark pool etc. Add user to SQL pool, similar to user1.
User Scenario3 –> user with reader role in subscription and added as pocsynapsereader from synapse studio.
If user’s role in subscription = reader then it gets inherited to workspace as reader. User3 is part of reader group, it gets following error while browsing synapse studio.
User Scenario4 –> user with reader role in subscription and added as spark pool admin
If user’s role in subscription = reader then it gets inherited to workspace as reader
Spark pool admin - with this access user will not be able to browse SQL Pool and also get following error while running notebook or browsing storage.