Microsoft KB Archive/300701

= BUG: BCP into Temporary Table Fails with Error 229 &quot;INSERT Permission Denied&quot; =

Article ID: 300701

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q300701



BUG #: 54529 (SQLBUG_70)

BUG #: 201022 (SHILOH_BUGS)



SYMPTOMS
When you use the bulk copy program (BCP) to copy data into a temporary table created by a user who is not the database owner (DBO) in the tempdb database, the operation may fail with the following error:

Server Error 229

INSERT permission denied on object '##table_name', database 'tempdb', owner 'dbo'.



CAUSE
This is a change in behavior from SQL Server 6.5.



WORKAROUND
You can work around this problem in the following ways:
 * If the BCP process is repetitive, you can create a permanent table with the BCP login mapped to a user who has permissions on the table.
 * You can give permissions to the users who intend to perform the BCP operation into the temporary table in tempdb.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0 and 2000.



MORE INFORMATION
The internal call made by the BCP process using DB-Library does its own permission checking. Because the user running BCP does not have permissions in tempdb, it is treated as a guest user. This was fine in SQL Server 6.5, where all temporary tables were created with the owner as guest. Due to a design change in SQL Server 7.0 and 2000, they are now created with the owner as DBO.

Additional query words: bulk insert data load

Keywords: kbbug kbpending KB300701

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.