Temp Table permissions... 1

[njvsummit]

I thought a table variable would cure my woes, but that doesn't seem to be a viable option with a bulk insert. I have the following stored procedure that works fine when I execute it (with admin access), but when my user runs it they recieve the following error: "The current user is not the database or object owner of table #Critical. Cannot perform SET operation." The user has execute permission on the procedure and I'm kind of at a loss.


CREATE procedure spoplst_criticaltotals

as

create table #OPLST(
(BATCH varchar(9),
SUBNO varchar(15),
PCNTLNO varchar(38),
CLAIMNO varchar(16),
CPLAN varchar(10),
SERVDATE datetime,
RECDATE datetime,
IN_FILE varchar(15))

bulk insert #OPLST
from '\\SERVER\oplst.txt'
with (FIELDTERMINATOR = ',', ROWTERMINATOR = '\r')

insert OPLST
(BATCHKEY,
BATCH,
PCNTLNO,
CLAIMNO,
CPLAN,
SERVDATE,
RECDATE,
INFILE)
select BATCHKEY = BATCH+CPLAN+RECDATE,
BATCH = BATCH,
PCNTLNO = convert(varchar(5), replace(replace(ltrim(replace(replace(PCNTLNO, ' ', '^'), '0', ' ')), ' ', '0'), '^', ' ')),
CLAIMNO = convert(varchar(5), replace(replace(ltrim(replace(replace(CLAIMNO, ' ', '^'), '0', ' ')), ' ', '0'), '^', ' ')),
CPLAN = CPLAN,
SERVDATE = convert(datetime, RECDATE, 112),
RECDATE = convert(datetime, RECDATE, 112),
IN_FILE = IN_FILE
from #Critical

GO

 

[SQLSister]

Permissions
Only members of the sysadmin and bulkadmin fixed server roles can execute BULK INSERT.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[njvsummit]

Thanks--should of caught that one.

 

[njvsummit]

OK. I added the user to the bulkadmin server role, but still received the same results. I went so far as creating a "real" table and granted the user Insert permission (since BOL stated that the user also needed to have insert permission on the table in addition to being granted the server role), but still no luck. I'm stumped.

 

[JamesLean]

Where is the table #critical coming from? The error refers to that rather than the BULK INSERT statement. Is this the whole SP code? I can't see where the table is being created?

--James

 

[SQLSister]

Good point, I never even noticed that you bulk inserted into one temp table and then refernced a different one in the insert to the real table.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[njvsummit]

The #Critical in both cases should have been #OPLST--I have two very similar procedures one using a temp table called #Critical and the other using #OPLST (I mismatched some of my cut and paste into the post), and I get the same results. However, I did find an answer (though not a solution) to my problem. It is listed as a bug in the MS knowledge base:

BUG #: 354213 (SHILOH_BUGS): Cannot Perform BULK INSERT with Bulkadmin Privileges

http://support.microsoft.com/default.aspx?scid=kb;en-us;302621&Product=sql2k).

Appearantly, even if the user is assigned to the Bulkadmin role and given insert permission on the table, the user still will not be able to bulk insert. The MS work around is to make sure that the user is also part of either the db_ddladmin or the db_owner database role in addition to the bulkadmin server role or to make the user the owner of the database. Not options in my case, so I'll need to find another solution.

Thanks

 

[JamesLean]

Thanks for posting your findings. I'm sure that may be useful for other users to know.

--James