Azure Data Bricks - User Doesn't have permission to perform this action while connecting to Azure Synapse Dedicate Pool

Question

We are connecting Azure Synapse Analytics - Dedicated Pool using the PySpark Code that runs from Azure Data Bricks using SQL Authentication.

While running, we are getting the below error when we use a user with db_datawriter and db_datareader role.

However, the errors get resolved only when we provide db_owner permission to the user. For obvious reasons, we can't assign db_owner permission that user.

Any leads to fix this issue with just db_datawriter and db_datareader roles?

Thanks,

Praveen Sreeram

Answer 1

Hi Praveen Sreeram,

Thanks for reaching out to Microsoft Q&A.

Yes you would need dbo permission. I am leaving the below links for you to go through.

https://learn.microsoft.com/en-us/answers/questions/567571/getting-user-does-not-have-permission-to-perform-t

https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Answer 2

Alright, after working with Microsoft team, we learnt about the below list of all access that we need to provide (definitely not db_owner)

--Make sure your user has the permissions to CREATE tables in the [dbo] schema

GRANT CREATE TABLE TO [<your_domain_user>@<your_domain_name>.com];

GRANT ALTER ON SCHEMA::<target_database_schema_name> TO [<your_domain_user>@<your_domain_name>.com];

--Make sure your user has ADMINISTER DATABASE BULK OPERATIONS permissions

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<your_domain_user>@<your_domain_name>.com];

--Make sure your user has INSERT permissions on the target table

GRANT INSERT ON <your_table> TO [<your_domain_user>@<your_domain_name>.com]

Ref: https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/synapse-spark-sql-pool-import-export?tabs=scala%2Cscala1%2Cscala2%2Cscala3%2Cscala4%2Cscala5

After providing all the above, it still didn't work as were using COPY command. So, we have to provide CONTROL permission on Dababase Level (CONTROL permission on individual schema also didn't work)

Ref: https://learn.microsoft.com/en-us/azure/databricks/archive/azure/synapse-polybase#--required-azure-synapse-permissions-for-polybase

Hope it helps

Answer 3

Hi Praveen Sreeram:

Glad to know that you were able to figure this out and thanks for sharing the rootcause. By the way, Microsoft Q&A community has a policy that "The question author cannot accept their own answer, they can only accept answers by others.". Hence request you to accept this as an answer so we can close this thread. I am summarizing the issue and root cause you have found for it.

https://learn.microsoft.com/en-us/answers/support/accept-answer

Issue Summary: Permission error when connecting Azure Synapse Analytics - Dedicated Pool using the PySpark Code that runs from Azure Data Bricks using SQL Authentication. But the error gets resolved only when we provide db_owner permission to the user. For obvious reasons, we can't assign db_owner permission that user.

Rootcause: Permissions at Granular level needed to fix this

Fix provided by MS:

GRANT CREATE TABLE TO [<your_domain_user>@<your_domain_name>.com];

GRANT ALTER ON SCHEMA::<target_database_schema_name> TO [<your_domain_user>@<your_domain_name>.com];

--Make sure your user has ADMINISTER DATABASE BULK OPERATIONS permissions

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<your_domain_user>@<your_domain_name>.com];

--Make sure your user has INSERT permissions on the target table

GRANT INSERT ON <your_table> TO [<your_domain_user>@<your_domain_name>.com]

Additional permission if using copy command:

Provide CONTROL permission on Database Level