Msg 4834 "You do not have permission to use the bulk load operator"

Question

I get this error no matter what I try.

I have a saved proc with execution as:

CREATE PROCEDURE usp_myproc WITH EXECUTE AS 'myuser' 

Within this paragraph I have

 EXEC('INSERT INTO ' + @tablename + ' SELECT col1, col2, col3 FROM OPENROWSET( BULK '''+ @filepath +''', FORMATFILE='''+ @formatfile +''', FIRSTROW=2 )as t' ) 

myuser has the role of bulkadmin, read / write, create a table, insert, select, execute, change permissions. Some of them may not be needed, but this is what I have tried so far. What am I missing?

Thanks.

Answer 1

As I said in a comment, server-level permissions are deleted the moment you use impersonation.

There are two ways around this:

Bad and fast way:

Set your database to ON. He will do his job. But if you do not fully understand what this is doing, then my advice should have been NOT to do this.

however, here is the code:

 ALTER DATABASE [YourDatabase] SET TRUSTWORTHY ON; 

Nice but slower way

This is much more accurate and does not have any unpleasant safety side effects.

What you do is that you sign your stored procedure with a certificate. You create a user from this certificate in the databasse. You give this user the correct permissions for your table in the database. You also create a login from the same certificate and grant them input permissions.

Since you sign a saved process with this certificate, every time sp is launched, it runs in the context of this user and registers where it is created from this certificate.

The following steps are performed:

• Create a certificate in the main

• create login from this certificate

• Granting Administrator Rights for this Login

Now you need the exact same certificate in your user database, so we have a few extra steps to complete

• Export certificate to disk

• Import the certificate into your user database

now we can complete the work

• create user from certificate
• grant table permissions to this user
• delete as a condition from the stored procedure
• Sign your stored procedure with a certificate

here is the code:

 USE master go CREATE CERTIFICATE BulkInsertCert ENCRYPTION BY PASSWORD = 'NicePassword!0' WITH SUBJECT = 'Gives Bulk Insert Privilegde' go CREATE LOGIN BulkInsert_CertLogin FROM CERTIFICATE BulkInsertCert go GRANT ADMINISTER BULK OPERATIONS TO BulkInsert_CertLogin go BACKUP CERTIFICATE BulkInsertCert TO FILE = '[your directory]\BulkInsertCert.cer' WITH PRIVATE KEY (FILE = '[your directory]\BulkInsertCert.pvk' , ENCRYPTION BY PASSWORD = 'EvenNicerPassword!0', DECRYPTION BY PASSWORD = 'NicePassword!0') go USE [YourDatabase] CREATE CERTIFICATE BulkInsertCert FROM FILE = '[your directory]\BulkInsertCert.cer' WITH PRIVATE KEY (FILE = '[your directory]\BulkInsertCert.pvk', DECRYPTION BY PASSWORD = 'EvenNicerPassword!0', ENCRYPTION BY PASSWORD = 'TheVeryBestPasswordThereIs!0') go --NOW DELETE THE CERTIFICATES FROM DISK CREATE USER BulkInsert_CertUser FOR CERTIFICATE BulkInsertCert go GRANT ALTER, INSERT ON [YourTable] TO BulkInsert_CertUser go ALTER PROCEDURE usp_myproc AS EXEC('INSERT INTO ' + @tablename + ' SELECT col1, col2, col3 FROM OPENROWSET( BULK '''+ @filepath +''', FORMATFILE='''+ @formatfile +''', FIRSTROW=2 )as t' ) -- Sign the test procedure each time you have changed it. ADD SIGNATURE TO usp_myproc BY CERTIFICATE BulkInsertCert WITH PASSWORD = 'TheVeryBestPasswordThereIs!0' go 

Final Note:

Please replace your directory with the path where you are sure that the sql service account has received write permission!

Make sure you delete the exported certificates after you configure.

Answer 2

Try granting the following server level permissions:

  GRANT ADMINISTER BULK OPERATIONS TO [server_login] 

In addition, there is another SO related only to this , please refer to this.