How to allow non-admin user to use BULK INSERT on Azure SQL Database

Question

I am currently struggling to get BULK INSERT working on an Azure SQL Database without using the admin user. The following script works fine and exactly as expected and documented when executed by the admin user:

-- Note: this requires a MASTER KEY to exist!
-- If you don't have a master key yet, create one with the following statement:
--CREATE MASTER KEY;

CREATE DATABASE SCOPED CREDENTIAL StorageCredential WITH
    IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '<my-blob-storage-sas>';

CREATE EXTERNAL DATA SOURCE StorageContainerDataSource WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://<blob-storage-account>.blob.core.windows.net/bulk',
    CREDENTIAL = StorageCredential
);

CREATE TABLE [#TestFacts] (
    [Id] uniqueidentifier,
    [MachineId] uniqueidentifier,
    [TimeZone] nvarchar(50),
    [SliceDate] datetime2(7),
    [SliceTimeStamp] datetime2(7),
    [Weight] bigint,
    [UserId] uniqueidentifier,
    [Longitude] float,
    [Latitude] float
);

BULK INSERT [#TestFacts]
FROM 'test.csv'
WITH (
    DATA_SOURCE = 'StorageContainerDataSource',
    FORMAT = 'CSV',
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '0x0a',
    KEEPIDENTITY,
    KEEPNULLS
);

DROP TABLE [#TestFacts];
DROP EXTERNAL DATA SOURCE StorageContainerDataSource;
DROP DATABASE SCOPED CREDENTIAL StorageCredential;

When executing this as a non-admin user, which has been granted SELECT, UPDATE, DELETE, INSERT, ALTER, CONTROL and ADMINISTER DATABASE BULK OPERATIONS, I get the following error message:

Msg 4834, Level 16, State 1, Line 26
You do not have permission to use the bulk load statement.

According to the documentation, it should be sufficient to have the permissions INSERT, ALTER and, most importantly, ADMINISTER DATABASE BULK OPERATIONS. But when checking the database permissions (in sys.database_permissions) I can see that GRANT ADMINISTER DATABASE BULK OPERATIONS TO myUser added the following row:

principal_id  name    type_desc  authentication_type_desc  class_desc  type  permission_name  state_desc  major_id  minor_id
6             myUser  SQL_USER   DATABASE                  DATABASE          NULL             GRANT       0         0

Note that type is empty and permission_name is NULL! According to the documentation, this should be DABO and ADMINISTER DATABASE BULK OPERATIONS.

So my questions right now are: Is this a bug in Azure SQL Database? Is there something I forgot to do? Is BULK INSERT actually not supported for non-admin users?

Some notes:

The @@VERSION is "Microsoft SQL Azure (RTM) - 12.0.2000.8 Jan 26 2018 23:35:00" and SERVERPROPERTY('EngineEdition') is "5".
All resources I found on similar issues talk about SQL Server, where you either need to have the role bulkadmin or need to grant ADMINISTER BULK OPERATIONS (note that DATABASE is missing there!) to the user. But Azure SQL Database has neither.

Simon Lehmann Simon Lehmann · Accepted Answer · 2018-03-20T12:56:10

Here is an example on the Microsoft github account which contains a very important piece of information:

Create a permanent table. A temp table currently is not supported for BULK INSERT, although it will will work with OPENROWSET

So the problem can be resolved by removing the # from the table name, to make it a permanent table. Then everything works as expected and documented, even for non-admin users.

Note: The quote from the example reads as if inserting into a temporary table would be possible with OPENROWSET (e.g. INSERT INTO ... SELECT ... FROM OPENROWSET(BULK ...) AS bulk), but testing that results in the same error as BULK INSERT!

How to allow non-admin user to use BULK INSERT on Azure SQL Database

1 Answers