BULK INSERT permission denied error when importing a CSV file into a SQL table

Question

I am trying to use BULK INSERT in the current version of SQL Server Express to import data from a multi-column CSV file into a SQL Server table on the same computer. I have used the following query to create the table and import data, but I keep getting an error message saying I do not have permission to use BULK INSERT:

CREATE TABLE NEW_TBL3 (
    prvdr_num VARCHAR(10),
    ffy VARCHAR(4),
    state_abbreviation VARCHAR(2))

BULK INSERT NEW_TBL3
FROM 'C:\Users\alcha\Documents\DATA\RAND5123.csv' 
WITH (
  FORMAT = 'CSV',
  FIRSTROW = 2,
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n',
  CODEPAGE = '65001',
  TABLOCK,
  KEEPIDENTITY,
  FIELDS(
    [prvdr_num] VARCHAR(50),
    [ffy] VARCHAR(50),
    [state_abbreviation] VARCHAR(50)
  )
);

Here is an example of the three columns from a table with several hundred columns that I would like to bring into SQL:

What could be causing this error, and how can I resolve it? I have tried various solutions suggested by CHAT GPT, but nothing seems to be working.

AI

Answer 1

Hi @AIC

To use the BULK INSERT statement, you need to have the server-level permission ADMINISTER BULK OPERATIONS or be a member of the fixed server role bulkadmin.

Try: GRANT ADMINISTER BULK OPERATIONS TO [USERNAME]

Rather than directly granting the right you could also add a user or group to the bulkadmin role.

Like this: ALTER SERVER ROLE [bulkadmin] ADD MEMBER [USERNAME]

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Note that to be able to run the GRANT command that Cosmog suggested, you need to have the permission to run them as well. You cannot grant permissons to yourself. You will need to talk with the person who is responsible for this server to give you these perimssions

"Cannot find the login 'dbo', because it does not exist or you do not have permission.".

This is not very surprising. dbo is not a very common name for a server login, but it is the name of the altmighty database user. That is, there is a distinction between server logins and database users. Permissions to run BULK INSERT is a server level permission.

Answer 3

Hi @AIC,

It is permission related issue.

Step #1: find your account:

select suser_sname();

Step #2: check your permissions:

EXEC sp_helpsrvrolemember 'bulkadmin';

And for the rest of it, just follow the @Erland Sommarskog suggestions.