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

• Microsoft SQL Server version 6.5

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 version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

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.

Additional query words:

Keywords          : kbusage SSrvAdmin SSrvGen kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: April 21, 1999