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:

2. List the permissions for your user in your Serverless database

select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id
where name = 'utkarsh2';

3. Make sure your user has the permissions to CREATE tables in the [dbo] schema

GRANT CREATE TABLE TO utkarsh2;
GO
GRANT ALTER ON SCHEMA::dbo TO utkarsh2;
GO

4. Now you can give the ADMINISTER DATABASE BULK OPERATIONS permissions to the user

GRANT ADMINISTER DATABASE BULK OPERATIONS TO utkarsh2;

Please follow the same and you will be able to bulk insert after this permission.

CodePudding user response：

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 datalahouse 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];