BUG: BCP into Temporary Table Fails with Error 229 "INSERT Permission Denied" (300701)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
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.
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbbug kbpending KB300701
©2003 Microsoft Corporation. All rights reserved.