Post reply

Bulk insert into a temporary table

  • November 20, 2001 at 8:03 pm

    #77559

    I am testing a security model implementation, but I've hit a roadblock. A stored procedure is owned by dbo in a particular database. It creates a temporary table and inserts using the bulk insert command. When testing with the user for our application, I get an ODBC error that says the table must be owned by the calling user in order to do bulk insert. I then tested this by creating a stored procedure owned by dbo that creates a temp table. Upon creation of the table, I specify the owner as the application user. I then add a bulk insert command. When I test this using the application account again, I get the same error. The owner of the temp table was specified such that it isn't the same as the owner of the stored procedure, but the end result is the same. I also added the application account to the bulk-insert admin role, to make sure it could do just that. I haven't found a solution to this as of yet, so any help would be appreciated. Please tell me if my statement of the problem isn't clear, as well.

    Thanks,

    Eric

  • November 20, 2001 at 10:06 pm

    #420264

    I think I understand. You want to give a user rights to bulk insert data using a stored procedure, correct?

    If so, I would create a permanent table and use BULK INSERT from T-SQL in the stored proc. The ownership chain is not broken and it shoudl work. If not, then I would create the temp table using the user account and then run a stored procedure, passing in that table name.

    Steve Jones

    steve@dkranch.net

  • November 21, 2001 at 5:05 pm

    #420311

    Thanks for the suggestion. I tested the passing of a temporary table for a very long time, and had some problems using the bulk insert command with a variable. I used the execute command, but the filename part needs to be in quotes. I used two quotes for this, but when I execute the stored procedure, it still has problems. Have you tested this and got it to work successfully? If so, do you have some sample code you can post? The permanent table solution also has merit, and might be the way we go here.

    Thanks,

    Eric

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

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