Azure Synapse – SQL Pool Serverless – You do not have permission to use the bulk load statement

If you are using Synapse Analytics with SQL Pool Serverless (former SQL Pool On demand) and you want to manage a notion of “principle of least privilege”, there are a few things to take into account.

The creation of a USER using for example an AD group:

CREATE USER [MyAdGroup] FROM EXTERNAL PROVIDER

With [MyAdGroup], a group in the Active Directory to which we want to give the minimum amount of rights in order to execute a query on our Synapse SQL Pool Serverless.

DATABASE SCOPED CREDENTIAL access

Indeed, if you define your access to storage accounts via a Shared Access Signature, you will need to create DATABASE SCOPED CREDENTIAL.

In this case, you will most likely have used the following syntax to create your data source:

CREATE MASTER KEY
CREATE DATABASE SCOPED CREDENTIAL [SasToken_MyStorage]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '?sv=2019-12-12&ss=bfqt&srt=sco&sp=rdlx&se=2099-09-02T21:03:45Z&st=2020-08-31T13:03:45Z&spr=https&sig=z11sZ9cHpPRf0MxxxxG8J9c%3D';
GO
CREATE EXTERNAL DATA SOURCE [MyDataSource]
WITH ( LOCATION = 'https://monstorage.blob.core.windows.net/insights-logs-pipelineruns/resourceId=/SUBSCRIPTIONS/',
CREDENTIAL = [SasToken_MyStorage]
)

To give rights to users on the Scoped Credential Database, you must therefore use:

GRANT CONTROL ON DATABASE SCOPED CREDENTIAL :: [SasToken_MonStorage] TO [MyAdGroup]

The view access

GRANT SELECT ON Object::[dbo].[Myvue]

That would be enough if you didn’t use something like:

SELECT 
  * 
FROM 
  OPENROWSET(
    BULK '/folder/*.parquet', DATA_SOURCE = [MaDataSource], 
    FORMAT = 'PARQUET'
  ) AS file

Because at this step, an error message appears:

You do not have permission to use the bulk load statement

ADMINISTER DATABASE BULK access

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [MyAdGroup]

Note that if you used an SQL Login you would not have the problem because it would be a member of the public role server level and would therefore have the right to use BULK …

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-overview?tabs=impersonation#permissions

Use a Login FROM EXTERNAL PROVIDER

As we have seen previously, using a Login solves the problem, it is therefore possible to create our User via a Login to have direct rights to BULK operations.

CREATE LOGIN [MyAdGroup] FROM EXTERNAL PROVIDER
CREATE USER [MyAdGroup] FROM LOGIN [MyAdGroup]