You do not have permission to use the bulk load statement error

Question

I am trying to insert an image into a VARBINARY(MAX) column. I get this error:

You do not have permission to use the bulk load statement.

Here is my code:

INSERT INTO Stickers (Name, Category, Gender, ImageData)
       SELECT   
           'Red Dress',
           'Dress',
           'F',
           BulkColumn FROM OPENROWSET(Bulk '\\Mac\Home\Documents\MMImages\reddress.png', 
                                      SINGLE_BLOB) AS BLOB

I realise there are a lot of answers on this topic but none of them have worked for me. This answer would be the easiest one for me to follow, however when using the object explorer and going into security > logins > right clicking my user does not reveal a "properties" menu item to go into.

I am using Sql Server Management Studio. Maybe I am not using the SQL Server version that I think I am, because none of the programmatic ways to set the permissions for my user worked. I think I am using SQL Server 2012. I probably have a few versions of SQL server on my computer. Clicking Help > About, it does show the logo "Microsoft SQL Server 2012" above the version information for various components (It does not show the version information for SQL Server here).

EDIT: Perhaps could someone please state the exact code I would use above my insert statement, given that the database is called MirrorMirror, the table is called Stickers, my user is called Amber, and my server is called gonskh1ou0.database.windows.net.

Mechanism

Answer 1

To make sure you have the right permissions to use BULK commands follow the below

• Expand Security
• Expand Logins
• Right click on your username and choose properties (A dialog window appears)
• Choose Server Roles
• Select bulkadmin to be able to use bulk commands or sysadmin to be able to use any commands to your database.

Now, in regards to the query your are using it's not quite right.

For creating the table

CREATE TABLE [dbo].[Stickers] (
        [name] varchar(10)
        , [category] varchar(10)
        , [gender] varchar(1)
        , [imageData] varchar(max)
)

For inserting the large value data

INSERT INTO  [dbo].[Stickers] ([name], [category], [gender], [imageData])
SELECT 'Red dress'
        , 'Dress'
        , 'F'
        , photo.*
FROM OPENROWSET(BULK 'C:\Users\username\Desktop\misc-flower-png-55d7744aca416.png', SINGLE_BLOB) [photo]

A couple of notes:

• You need to set a correlation name for the bulk rowset after the FROM clause ([photo])
• Use the right column prefix that has been used for the correlation of the bulk rowset (photo.*)
• The column for the bulk insert needs to be set as varchar(max)

MSDN article for this: here

Cupp

Thanks. Why does properties not show as an option to click when I right-click on my username? – Mechanism

@user3935156 - You will either have to connect to the server with an administration account or contact your administrator in order to make any sort of server security changes to any username. – Cupp

I am the administrator. I thought I must not be, so I verified that I am in my Microsoft Azure page for the server. – Mechanism

Answer 2

ALTER SERVER ROLE [bulkadmin] ADD MEMBER [user]

Rutan

I also had to give alter permission on target table – Arcane

Answer 3

Possibly one needs

ALTER ROLE [sysadmin] ADD MEMBER [user]

For example - in the current Linux version of SQL Server bulkadmin role is not supported and one needs to use a sysadmin to BULK INSERT. It is a known issue with SQL Server https://github.com/MicrosoftDocs/sql-docs/issues/4198 - SQL Server on Linux has permissions and roles that are not granular enough.

Asch

Answer 4

Here's another option that isn't in anyone else's answers yet and isn't in the Microsoft documentation:

Azure SQL requires the following:

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [user];

Note the special word DATABASE in that command, which is different from what the documentation says.

Note also that only the sysadmin account will have permissions to bulk insert into a temporary table. Any other user granted permissions using the command above will only be able to bulk insert into non-temporary tables.

Delineator