CREATE TABLE permission denied in database '<database name>' when using BULK Insert

Node-RED v3.0.2
MSSQL-PLUS 0.10.2
NodeJS 16.17.1

I am trying to make a BULK Insert in a table from a database where I have proper rights.

My table has 8 fields:

  • ID = Primary key auto-increment
  • uid = nchar(36), not null
  • filename = nchar(100), not null
  • creationDate = datetime, not null
  • conversionDate = datetime, null
  • uploadedDate = datetime, null
  • percentage = int, null
  • deleteDate = datetime, null

I am only inserting the following rows:

  • uid
  • filename
  • creationDate
  • percentage

I get the following error when I run it:

image

Is there something I do wrong?

Hi @hameljc

The error is exactly what it says.

The account you are using to connect to MsSQL does NOT have the rights to create a table on the target database.

You need to review the permissions on that database or advise your DBA to allow it

The error is from the SQL Server its self.

That I understand but I am doing a bulk insert. Why would I get an error about table creation on a bulk insert?

That is what is confusing me.

Does doing bulk insert need table creation rights?

Presently it would seem yes.

Thank you for the explanation....I will get the proper rights.

1 Like

So it seems that to do a bulk update, the table musn't exist as it creates it even if it exist.
Did I miss something?

My table is PP7ToConnect.

I have tried:

  • PP7ToConnect
  • [ConnectWorkflow].[dbo].[PP7ToConnect]

In all cases it creates the table. I wan to insert into an existing table. Any thoughts?

Nevermind, I hadn't read your code...so this is how it is. Ok will try something else.

You will need to raise it as an issue with the author.
According to the source code - it will create the table when using bulk insert commands.

Yes I know...I also know that he, the author, put a comment to make the table creation an option. So telling it that it doesn't do it the way I want seems pointless.

Although I will tell him that the documenation should definitely state this.

Thanks for all your helps.

@Steve-Mcl,

Maybe stating in the documentation/help that using bulk insert will create a table, even if one already exist.

Unless something has changed since I last worked on that node, the table will only be created if it does not exist. If the table exists then it will be used. That was the intention of the feature.

The .create = true flag was set true until it could be later exposed. Not because it could be used to "bulk full an existing table" but to "throw an error if the table doesn't exist". The difference is subtle but meaningful.

From the docs: mssql - npm

TIP: If you set table.create to true, module will check if the table exists before it start sending data. If it doesn't, it will automatically create it. You can specify primary key columns by setting primary: true to column's options. Primary key constraint on multiple columns is supported.

My guess is there is some other issue

Could it be because I don't put all fields of the table and only some and the code doesn't reckonize the table because of that?

I will try with all fields and put my results here.

I don't know anything about mssql, but if it is doing a 'create table if not exists' then perhaps you need create permissions even if it doesn't actually end up creating the database.

Verifying this in the mssql, tedious & SQL server documentation will be like finding a needle in a haystack in a field of haystacks.

A quick test should be easy enough to setup.

@hameljc could try it with a user with create permissions and see if that removes the problem.

I now have the CREATE TABLE permission which is why I came back to this post asking why it was creating a table already existing.

So I went another way and build a string of all the inserts I had to do so to do it in 1 query.

Was it creating a new table if it already existed, or was it just that it needed the permissions in case it had to?
Did the user have ADMINISTER BULK OPERATIONS permission?

AFAIK you cannot create a table that already exists. A SQL error should occur. Did you spell it correctly? Is it really in dbo?

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.