Post reply

BULK INSERT permission

  • 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?

  • September 8, 2011 at 3:24 pm

    #1378926

    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

  • September 8, 2011 at 10:56 pm

    #1378997

    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)
    Intro to Tally Tables and Functions

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

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