Sql Serverless database (Azure Synapse Analytics): You do not have permission to use the bulk load statement

Question

I'm getting getting a "You do not have permission to use the bulk load statement" in a Sql Serverless database (Azure Synapse Analytics).

I tried various methods to no avail:

EXEC master..sp_addsrvrolemember @loginame = N'%username%', @rolename = N'bulkadmin'

from: You do not have permission to use the bulk load statement

and

GRANT ADMINISTER BULK OPERATIONS TO username

from: SQL Server - You do not have permission to use the bulk load statement

None of these seem to work in the Sql Serverless context

I ended up just using a db_owner and db_denydatawriter combo like this:

ALTER ROLE db_owner ADD MEMBER [username]; -- owner allows bulk operations
ALTER ROLE db_denydatawriter ADD MEMBER [username]; -- deny writer in our specific case

This works, but it just doesn't feel right to make this particular user db_owner. Does anybody know of a better way to do this?

I'm trying to do a simple SELECT on a VIEW referencing a delta table (haven't tried it for parquet, but I'm expecting the same). I'm guessing SQL Serverless uses bulk load for this operation.

I'm doing this using System.Data.SqlClient (v5.x) from a Function App, with a Managed Identity. I don't think that matters much, as it's really a SQL roles issue.

##EDIT## The proposed solutions are pretty much the same. I tried this but it doesn't seem to work.
As mentioned before, I'm using a Managed Identity to connect to the database, maybe that's the cause of the current issue.

The SQL statements to grant the permissions work fine, and the user seems to have all the correct permissions when I query the sys tables. However, when executing from the Function App I now receive the following Exception:

Cannot find the CREDENTIAL 'datahub_api', because it does not exist or you do not have permission.
Could not use view or function 'HR.Employee' because of binding errors."

This credential does exist however. Adding my user to db_owner makes the Exception go away.

I'm using the following to create the credential:

CREATE DATABASE SCOPED CREDENTIAL [datahub_api] WITH IDENTITY='Managed Identity'

CREATE EXTERNAL DATA SOURCE "datalakehouse" WITH (

    LOCATION = '<url to datalakehouse storage>',

    CREDENTIAL = datahub_api

);

The user is created like this:

CREATE USER [username] FROM EXTERNAL PROVIDER;

##EDIT## Also worth mentioning is that I tried granting alter on the [HR] schema as well. Don't think this is necessary though, because I think this permission is only needed for some behind the scenes temp tables when doing the bulk load. Not sure about this so don't quote me on it :-)

Answer 1

I just had a little chat with one of our data engineers and we found the solution I was looking for.

So first we create the CREDENTIAL and USER as mentioned before:

CREATE DATABASE SCOPED CREDENTIAL [datahub_api] WITH IDENTITY='Managed Identity'

CREATE EXTERNAL DATA SOURCE "datalakehouse" WITH (

    LOCATION = '<url to datalakehouse storage>',

    CREDENTIAL = datahub_api

);

CREATE USER [username] FROM EXTERNAL PROVIDER;
/*
username is the same as the function app name in our case; 
this is the name of the managed identity created by azure when creating a System Assigned Managed Identity
*/

Next we grant the following permissions:

GRANT SELECT TO [username] ;

GRANT CONTROL ON DATABASE SCOPED CREDENTIAL :: [datahub_api] to [username];

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [username];

And then we threw in a denywriter, just for good measure:

ALTER ROLE db_denydatawriter ADD MEMBER [username];