September 8, 2002 at 7:30 pm
Hi all
I have a stored proc that
a) creates a # temp table
b) executes a simple dynamic sql statement that uses bulk insert to write the contents of the file to the temp table (which, btw, is blisteringly fast)
as follows:
CREATE PROCEDURE [dbo].[LoadLodgementEnrolmentData_sp] @lodgement_id integer, @filename varchar(500) AS
<create #mytable here>
set @v_sql = 'BULK INSERT #lodgement_enrolment FROM ''' + @filename + ''' WITH (FORMATFILE = ''d:\upload\lodgement.fmt'')'
exec(@v_sql)
<etc>
The user executing the stored proc has bulk insert role privs. Even so, the exec statement fails telling me the user doesnt have access to the #lodgement_enrolment temp table. I can only get it to work when ive allocated the user db_owner privs to the tempdb database (which I dont like at all). Ive tried other privs etc but all have drawn a blank, ive even tried creating the temp table via myuser.#lodgement_enrolment but this doesnt work as id tend to expect.
Anyhow, my question is, how can i utilise the temp table without granting tempdb db_owner access?
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 9, 2002 at 12:37 am
Hi Chris,
I have a simular problem, the only thing i could find is Q302621, i don't know when this bug is solved. maybe somebody has found an other workarround
regards,
Klaas-Jan
September 9, 2002 at 6:22 am
Interesting. Dont suppose the files are the same each time so you can dispense with the temp table? Or BCP instead?
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply