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

Praveen Sreeram 1 Reputation point
2024-06-07T11:26:00.4933333+00:00

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.

User's image

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

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,613 questions
Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
2,042 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vinodh247-1375 12,506 Reputation points
    2024-06-07T13:28:42.3466667+00:00

    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://video2.skills-academy.com/en-us/answers/questions/567571/getting-user-does-not-have-permission-to-perform-t

    User's image

    https://video2.skills-academy.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.

    0 comments
  2. Praveen Kumar Sreeram 0 Reputation points
    2024-06-26T06:21:46.3466667+00:00

    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://video2.skills-academy.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://video2.skills-academy.com/en-us/azure/databricks/archive/azure/synapse-polybase#--required-azure-synapse-permissions-for-polybase

    Hope it helps

    0 comments