BULK INSERT permission

Question

BULK INSERT permission

BW_Toro

Old Hand

Points: 340
More actions
September 8, 2011 at 11:44 am

#243000

I have an ETL stored procedure with a TRUNCATE TABLE statement and a BULK INSERT statement.
I use WITH EXECUTE AS 'dbo' in order to allow the TRUNCATE TABLE statement for anybody who calls the stored procedure.
I have not yet been able to make the BULK INSERT statement work. The error message is...
"You do not have permission to use the bulk load statement."
The 'dbo' user is mapped to the 'sa' login, which has the sysadmin server role.
I have tried a few things to make this work, but I must be missing something.
Why would I not have permission to use the bulk load statement?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

dbo is a database-level principal. To my knowledge it will not track "upwards" to the Server Login to gain the caller bulk insert permission.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

BW_Toro (9/8/2011)
I have an ETL stored procedure with a TRUNCATE TABLE statement and a BULK INSERT statement.
I use WITH EXECUTE AS 'dbo' in order to allow the TRUNCATE TABLE statement for anybody who calls the stored procedure.
I have not yet been able to make the BULK INSERT statement work. The error message is...
"You do not have permission to use the bulk load statement."
The 'dbo' user is mapped to the 'sa' login, which has the sysadmin server role.
I have tried a few things to make this work, but I must be missing something.
Why would I not have permission to use the bulk load statement?

Try WITH EXECUTE AS OWNER and rebuild the proc while logged in as your designated "Promotion Code Login" which should be a member of the "SA" role. If you don't have such a login, you should take the time to build one. It should only be used for promoting production code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)