MSSQL Server - Error: invalid object name

[JonC]

When performing a query of a MS SQL Server database you receive the following error "invalid object name". This error is generated when a table is pre-fixed with the schema other than dbo (example: myuser.table).

Querying "select * from myuser.table" would allow you to query the table but is not convenient.

Instead the table should be pre-fixed with 'dbo'. to do this you will need to ALTER each of the tables with this statement:

ALTER SCHEMA dbo TRANSFER myuser.table

To generate all the needed SQL to change ALL tables:

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name  
FROM sys.tables p INNER JOIN  
sys.Schemas s on p.schema_id = s.schema_id  
WHERE s.Name = 'EXISTING_OWNER_USERNAME'

[jakeh]

If you need to update SQL Server Objects like stored procedures, please change 'sys.tables' in the above query to 'sys.objects'.

[JonC]

For SQL 2000 change the owner on a table:
1. Change MyDB to your database name.
2. Change MyUser to the current owner username.
3. Change MyTableName to the table needing modified.

use MyDB

EXEC sp_changeobjectowner 'MyUser.MyTableName', 'dbo'