Bulk Insert - Cannot perform SET operation.

I am trying to use Bulk Insert for a user that is not sysadmin.
I have already set up the user as a member of "bulkadmin".

When I run the following script:
DECLARE @.SQLVARCHAR(1000)
CREATE TABLE amdbo.#temp (
[id] [varchar] (10) NULL
,[fld2] [varchar] (10) NULL
,[fld3] [varchar] (10) NULL
)
set @.SQL =
'BULK INSERT amdbo.#temp
FROM ''F:\test.txt''
WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
= ''\n'')'
EXEC (@.SQL)
select * from #temp

I still get the message ...
Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of table '#temp'.
Cannot perform SET operation.

Anyone have an idea what I am doing wrong?
Drew.Drew (cladre@.hotmail.com) writes:
> I am trying to use Bulk Insert for a user that is not sysadmin.
> I have already set up the user as a member of "bulkadmin".
> When I run the following script:
> DECLARE @.SQL VARCHAR(1000)
> CREATE TABLE amdbo.#temp (
> [id] [varchar] (10) NULL
> ,[fld2] [varchar] (10) NULL
> ,[fld3] [varchar] (10) NULL
> )
> set @.SQL =
> 'BULK INSERT amdbo.#temp
> FROM ''F:\test.txt''
> WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
>= ''\n'')'
> EXEC (@.SQL)
> select * from #temp
> I still get the message ...
> Server: Msg 8104, Level 16, State 2, Line 1
> The current user is not the database or object owner of table '#temp'.
> Cannot perform SET operation.

As I recall you cannot bulk load to temp tables if you are not sysadmin.
Or, this is somewhat weird: the user is the dbo of tempdb.

It's probably better to create a permanent table that this user is
the owner of and use that table as the target.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The solution I've done is to create a physical table, populate it, and
when the procedure ends, drop the table. Which is pretty much what
you suggested.
Thanks,
Drew

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9402F24BBF061Yazorman@.127.0.0.1>...
> Drew (cladre@.hotmail.com) writes:
> > I am trying to use Bulk Insert for a user that is not sysadmin.
> > I have already set up the user as a member of "bulkadmin".
> > When I run the following script:
> > DECLARE @.SQL VARCHAR(1000)
> > CREATE TABLE amdbo.#temp (
> > [id] [varchar] (10) NULL
> > ,[fld2] [varchar] (10) NULL
> > ,[fld3] [varchar] (10) NULL
> > )
> > set @.SQL =
> > 'BULK INSERT amdbo.#temp
> > FROM ''F:\test.txt''
> > WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
> >= ''\n'')'
> > EXEC (@.SQL)
> > select * from #temp
> > I still get the message ...
> > Server: Msg 8104, Level 16, State 2, Line 1
> > The current user is not the database or object owner of table '#temp'.
> > Cannot perform SET operation.
> As I recall you cannot bulk load to temp tables if you are not sysadmin.
> Or, this is somewhat weird: the user is the dbo of tempdb.
> It's probably better to create a permanent table that this user is
> the owner of and use that table as the target.|||I have the similiar issues encountered. Please refer to MS
knowledgebase 302621. Microsoft has confirmed this is an software
issue.

cladre@.hotmail.com (Drew) wrote in message news:<d00212d6.0310200700.3b2bca9f@.posting.google.com>...
> The solution I've done is to create a physical table, populate it, and
> when the procedure ends, drop the table. Which is pretty much what
> you suggested.
> Thanks,
> Drew
>
> Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9402F24BBF061Yazorman@.127.0.0.1>...
> > Drew (cladre@.hotmail.com) writes:
> > > I am trying to use Bulk Insert for a user that is not sysadmin.
> > > I have already set up the user as a member of "bulkadmin".
> > > > When I run the following script:
> > > DECLARE @.SQL VARCHAR(1000)
> > > CREATE TABLE amdbo.#temp (
> > > [id] [varchar] (10) NULL
> > > ,[fld2] [varchar] (10) NULL
> > > ,[fld3] [varchar] (10) NULL
> > > )
> > > set @.SQL =
> > > 'BULK INSERT amdbo.#temp
> > > FROM ''F:\test.txt''
> > > WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
> > >= ''\n'')'
> > > EXEC (@.SQL)
> > > select * from #temp
> > > > I still get the message ...
> > > Server: Msg 8104, Level 16, State 2, Line 1
> > > The current user is not the database or object owner of table '#temp'.
> > > Cannot perform SET operation.
> > As I recall you cannot bulk load to temp tables if you are not sysadmin.
> > Or, this is somewhat weird: the user is the dbo of tempdb.
> > It's probably better to create a permanent table that this user is
> > the owner of and use that table as the target.