You do not have permission to use the bulk load statement

Question

Hello,

I have a SQL server (on my server) with 20-30 databases.
Each database has different users (one user per database).
In one database I created a stored procedure with "BULK INSERT" that select content from CSV file (on the same server) and do stuff.
When I run the stored procedure with my user it works fine, but with the database user I get the error "You do not have permission to use the bulk load statement".

How can I open this permission only to this database ?

This is the part of the BULK INSERT:

 set @SQL = '  
 BULK INSERT #Tbl_Csv  
 FROM ''' + @CsvFileFullName + '''    
 WITH (  
 firstrow = 2,  
 fieldterminator = '','',  
 rowterminator=''\n'',  
 batchsize=10000,  
 maxerrors=10  
 );  
 '  
 EXEC (@sql)  

Answer 1

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.

So you need to do:

   USE master  
   go  
   GRANT ADMINISTER BULK OPERATIONS TO user_for_that_database  

It is also possible to package the permission with the stored procedure, but I skip the description of how to do this, unless you actually need it.

Answer 2

Hi @nd0911 ,

In addition to the @Erland Sommarskog method there is another one.
There is a server-level role called bulkadmin that does that. Rather than directly granting the permission to a user account you could add a user or group to that role.

Please see below how to find that server role.
Go to its properties and add role members.

Answer 3

what is it means to "to package the permission with the stored procedure", do you mean that the permission will be available for only this stored procedure ?

Yes. If you grant the bulk permission to the user, this means that the user can load any file on the server, of which some may include sensitive data. Say that you only want the user to be able to load files in a certain folder. Then you could write the SP so that it validates the input pat and only accepts permitted paths.

But if you are not uncomfortable with granting the database user the server-level permission, there is little reason to dabble with this. I only mentioned it in case granting a server-level permission would be a a concern to you.

An addition to the @Erland Sommarskog method there is another one.

There is a server-level role called bulkadmin that does that. Rather than directly granting the permission to a user account you could add a user or group to that role.

I did mention bulkadmin in my post, but I preferred to give the example with the explicit permission. It's more or less a toss-up which one to use.

Yitzhak make a good point about roles. Rather than granting permission directly to the user, you could do:

   USE master  
   go  
   CREATE SERVER ROLE bulkusers  
   GRANT ADMINISTER BULK OPERATIONS TO bulkusers  
   ALTER SERVER ROLE bulkusers ADD MEMBER user_for_that_database  

If the need would arise for another database, you can simply add that database user to bulkusers.

can you please guide me how can I do it, because in the server level tree "security > logins" I don't have this database user in the list, I have this user in the database tree "security > users"...

The one situation where the above would not work would be if you have created these database users as database users WITHOUT LOGIN. Keep in mind is that all you told us is that you have these database users, but you did not tell us how they have been set up.

Answer 4

Yes, you are right, I want to give this user access only to the folder of this CSV file, not more not less.

In that case, we don't have to discuss why the user does not show up among the logins.

The first step is to write the procedure so that it validates the path, one way or an other. That is, @csvFullFileName in your original post must not be a parameter to the stored procedure.

The short recipe to package the permission with the procedure is as follows:

1. Create a certificate in the user database.
2. Sign the procedure with the certificate.
3. Drop the private key from the certificate.
4. Export the certificate to the master database.
5. Create a login from that certificate. (This is a special type of login that cannot actually log in.)
6. Grant this login ADMINISTER BULK OPERATIONS (or add it to the bulkadmin role).

There may several things above that are new to you. Rather explaining this in detail here, I refer you to an article on my web site: https://www.sommarskog.se/grantperm.html.

That's a not a short article, as I take the time to explain all the mechanisms involved. You don't need to read the full article for your task, only the first five chapters, but that is still more than a mouthful. If you want to get started quickly without understanding of what is going on, you can try the script at https://www.sommarskog.se/grantperm/GrantPermsToSP_server.sql.txt. I explain this script in the article.