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 ?
Greg Galloway commented
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