Bulk Insert fail for TempTable (bulkAdmi)

Hi,
I am trying to create a temp table and bulk insert data into that temp table
.
I am using the 'bulkinsert' login is a part of a server role bulkadmin and
has read access to databaseA.
Create table #My_Table
( Col1 varchar(14),
Col2 varchar(60))
then trying to bulk insert using
BULK INSERT #My_Table FROM '\\Network1\Data\TestDoc.txt' WITH
(Rowterminator='\n', FieldTerminator=',')
But it is giving me following error.
The current user is not the database or object owner of table '#My_Table ".
Cannot perform SET operation.
My 'bulkinsert' login has Access to DatabaseA, and also to tempdb.
Anyone have any idea why this is happening.. ? Your help is much appreciated
.
Thank youbluefish (bluefish@.discussions.microsoft.com) writes:
> I am trying to create a temp table and bulk insert data into that temp
> table.
> I am using the 'bulkinsert' login is a part of a server role bulkadmin
> and has read access to databaseA.
> Create table #My_Table
> ( Col1 varchar(14),
> Col2 varchar(60))
> then trying to bulk insert using
> BULK INSERT #My_Table FROM '\\Network1\Data\TestDoc.txt' WITH
> (Rowterminator='\n', FieldTerminator=',')
> But it is giving me following error.
> The current user is not the database or object owner of table
> '#My_Table ".
> Cannot perform SET operation.
> My 'bulkinsert' login has Access to DatabaseA, and also to tempdb.
> Anyone have any idea why this is happening.. ? Your help is much
> appreciated.
This is a flaw in SQL Server. Temp tables are "owned" by uid = 1 in
sysobjects, so the user needs dbo rights in tempdb (which I don't
recommend.) You will have to stick to a permanent table.
See also
5cc04c2cf" target="_blank">http://groups.google.com/group/comp...r />
5cc04c2cf
ab18661
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you very much!
"Erland Sommarskog" wrote:

> bluefish (bluefish@.discussions.microsoft.com) writes:
> This is a flaw in SQL Server. Temp tables are "owned" by uid = 1 in
> sysobjects, so the user needs dbo rights in tempdb (which I don't
> recommend.) You will have to stick to a permanent table.
> See also
> http://groups.google.com/group/comp...3&hl=sv#5cc04c2
cfab18661
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Also found out that ddl_admin permission to tempdb works as well.
Thank you
Sam
"Erland Sommarskog" wrote:

> bluefish (bluefish@.discussions.microsoft.com) writes:
> This is a flaw in SQL Server. Temp tables are "owned" by uid = 1 in
> sysobjects, so the user needs dbo rights in tempdb (which I don't
> recommend.) You will have to stick to a permanent table.
> See also
> http://groups.google.com/group/comp...3&hl=sv#5cc04c2
cfab18661
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||bluefish (bluefish@.discussions.microsoft.com) writes:
> Also found out that ddl_admin permission to tempdb works as well.
OK, that slightly better than db_owner rights, but not much.
It appears that this issue has been addressed in SQL 2005.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx