Msg 4834 "You do not have permission to use the bulk load statement" [closed]

Question

I am getting this error no matter what I try.

I have a stored proc with execute as:

CREATE PROCEDURE usp_myproc
WITH EXECUTE AS 'myuser'

Within this s.p. I have

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

The myuser does have bulkadmin role, read/write, create table, insert, select, execute, alter permissions. Some of them might not be needed, but that's what I've tried so far. What am I missing?

Thank you.

Answer 1

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

There are 2 ways around this:

The bad and quick way:

Set your database thrustworthy to ON. It will get the job done. But if you don't fully understand the what this does, then my advise would be to NOT do this.

however, here is the code:

ALTER DATABASE [YourDatabase] SET TRUSTWORTHY ON;

The good but slower way

This is much more precise and hasn't got any nasty security side effects.

What you do is that you sign your stored procedure with a certificate. You create a user from that certificate in the databasse. You give that user the proper permissions on your table in the database. You also create a Login from the same certificate and grant that login the bulk permissions.

Because you sign the stored proc with that certificate, everytime the sp gets executed, it's executed in the context of that user and login that where created from that certificate.

the steps are:

1. Create certificate in master

2. create a login from that certificate

3. Grant Bulk admin permissions to that login

Now you need exactly the same certificate in your user database so we have some extra steps to do

1. Export the certificate to disk

2. Import the certificate into your user database

now we can finalize

1. create user from the certificate
2. grant permission on table to that user
3. remove execute as clause from your stored procedure
4. Sign your stored procedure with your 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 a path where you are sure that the sql service account has got permission to write!

Make sure you delete those exported certificates after you have done setting up..

Answer 2

Try granting the following server level permission:

    GRANT ADMINISTER BULK OPERATIONS TO [server_login]

Also, there is another SO related just to this, please refer to that.