Microsoft KB Archive/169492

= FIX: Cannot Load a Table If the Owner Is Not SA or DBO =

Article ID: 169492

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q169492



BUG #: 16956 (NT: 6.50)



SYMPTOMS
A table cannot be restored from a database or table dump by anyone other than the target table owner if the target table is not owned by the system administrator (SA) or database owner (DBO). This is true even for the SA or DBO of the database; that is, the SA or DBO cannot load a table into a table that a user owns. The LOAD TABLE statement fails with the following error message:

Msg 8407, Level 16, State 1 Invalid target table 'newmy2' specified in LOAD TABLE. Table load has been aborted for table 'newmy2'.



WORKAROUND
To work around this problem, do either of the following:
 * If you are the DBO or SA, use the SETUSER statement to impersonate the table owner and then execute the LOAD TABLE statement.

-or-
 * Create a new table, load the data into this table, and then use INSERT SELECT to copy the data into the target table.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.



MORE INFORMATION
The following are the requirements to load a table. As long as they are satisfied, you should be able to load a table:
 * You must have INSERT permission on the target table.

-and-
 * The target table's database must have the SELECT INTO/bulkcopy database option set before loading a table.

Keywords: kbbug kbfix kbusage KB169492

-

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

© Microsoft Corporation. All rights reserved.