How can we improve Microsoft Azure SQL Data Warehouse ?

Azure Data Factory loading to Azure DWH - Polybase permissions

When using Polybase to load into Data Warehouse via Data Factory, Control permission on the database is required for the user.
Can this be limited to a Schema Owner, or be more granular at the database level ?

10 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

Rob Barat shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • Greg Galloway commented  ·   ·  Flag as inappropriate

    I did some research on this and totally agree. It seems like a very small enhancement to SQL DW would be all that's needed.

    If SQL DW could add support for the following GRANT statement then we could do ADF and Polybase with reasonable permissions. Please support:

    GRANT ALTER ANY DATABASE SCOPED CREDENTIAL to adf_user

    Once that is done, then the following existing grants which are currently working will provide enough permissions to ADF to use Polybase I think:

    --if some admin has previously run CREATE MASTER KEY then all ADF needs is to select from sys.symmetric_keys where symmetric_key_id = 101 to confirm that's done already
    GRANT VIEW DEFINITION TO adf_user

    --allows ADF to create a new external data source
    GRANT ALTER ANY EXTERNAL DATA SOURCE TO adf_user

    --allows ADF to create a new external file format
    GRANT ALTER ANY EXTERNAL FILE FORMAT TO adf_user

    --gives ADF permissions needed to the dbo schema where ADF currently puts external tables
    GRANT ALTER ON SCHEMA::dbo TO adf_user

    --gives ADF permissions to create an external table
    GRANT CREATE TABLE TO adf_user

Feedback and Knowledge Base