SQL Server Bulk Insert fails when it is in a stored procedure

Question

I'm trying to import some PDF files into a temp table.

I created a stored procedure for that purpose and I use this code:

SET @SqlCommand = 'INSERT INTO ZENVIOBOLETO (IDBOLETO,NomeArquivo, image_data) SELECT '+CAST(@IDBOLETO AS VARCHAR(10))+','''+@NomeArquivo+''',image_data FROM OPENROWSET(BULK N'''+@Pasta+@FileName+''''+',SINGLE_BLOB) AS ImageSource(image_data);';

This code, if I use the print @SqlCommand, returns this output:

INSERT INTO ZENVIOBOLETO (IDBOLETO,NomeArquivo, image_data) 
    SELECT 803, '20162744', image_data 
    FROM OPENROWSET(BULK N'c:\RM\Boleto.1.803.PDF', SINGLE_BLOB) AS ImageSource(image_data);

When I try to run my stored procedure, however, I get an error:

You do not have permission to use the bulk load statement

But when I use the command line in SSMS I don't get any error.

The stored procedure is running as the same user, I also tried this:

ALTER PROCEDURE [dbo].[ACERTANOMEBOLETOS](@Pasta VARCHAR(100), @CODCOLIGADA INT)
WITH EXECUTE AS 'suporte'

I really don't get what is happening, inside the stored procedure I got permission error, outside it, I don't get. Same user, same code.

Additionally, the user is a member of bulkadmin and has the "ADMINISTER BULK OPERATIONS" permission.

For this reason I can run the command directly in SSMS.

The question is, why do I get directly into SSMS and not the stored procedure?

Any insights for me?

Answer 1

So, there is 2 ways to solve it:

1. As said, if the database is checked for TRUSTWORTHY it will works;
2. Give permission to the users in xp_cmdshell and remove the EXECUTE AS

I did the second option because we will have just 2 users using the stored procedure.

Thanks!

Answer 2

Did you check the 'trustworthy' database option? It must be set to 'ON' for such operations from outside. If no, try this:

ALTER DATABASE <your_db_name> SET TRUSTWORTHY ON;