February 27, 2007 at 6:59 am
Hi everyone,
I changed the ownership of a table using sp_changeobjectowner,
and something odd occures.
In enterprise manager I can open the table and return all rows. However regardless of what account I use to open query analyzer and launch a select from statement for the changed owner table, I receive “an invalid object” indication. It’s like query analyzer doesn’t acknowledge the existence of the table. I can query all other tables in the DB.
Any ideas?
SQL 2000 SP4
February 27, 2007 at 7:07 am
Ed
If the new owner isn't your username or dbo then you need to qualify the object name with its new owner.
SELECT * FROM newowner.MyTable
John
February 27, 2007 at 7:44 am
Thanks for responding John. Even though I use the new owner (wyantetest.changetableowner), or (hboffice.wyantetest.changetableowner) wich includes the domain, I receive the same error. Any ideas?
Thanks,
Ed
February 27, 2007 at 7:47 am
Are you sure you are running this on the correct database, on the correct server?
February 27, 2007 at 7:49 am
affimative
February 27, 2007 at 7:52 am
Ed
Who does Enterprise Manager show as being the owner of the table?
John
February 27, 2007 at 7:52 am
Little on the extreme side, but are you able to drop and recreate the objects with the correct username :
Create table wyantetest.tblname...
February 27, 2007 at 8:06 am
Ninja,
Yes I can drop and recreate new objects the the wyantetest username, but
Not the changetableowner object, it still indicates “not a valid object”.
Jim,
EM indicates wyantetest as the owner.
February 27, 2007 at 8:18 am
Ed
OK - so the object in question is definitely a table and not a view or stored procedure or function? What is the result of this query:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME = 'changetableowner'
John
February 27, 2007 at 8:35 am
John,
It is a table and not a view or stored procedure or function.
The results of the your query are two columns, one table_schema and
the other is table_name, and no row information.
R.,
Ed
February 27, 2007 at 8:43 am
Ed
It sounds like the table doesn't exist at all under any owner, then. Have you refreshed the window in Enterprise Manager?
Try this query:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA = 'wyantetest'
John
February 27, 2007 at 8:57 am
John,
Yes, I do a refresh after every modification. The new query returns
the same results. Interesting when I replace 'wyantetest' with
any other table object or security login, it returns the same results.
When I replace the query with an object other than wyantetest or
changetableowner, should it return some rows? Seems like it
should?
R.,
Ed
February 27, 2007 at 9:04 am
Ed
The first query returns all tables with the name specified; the second returns all tables owned by the user specified. If you do SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' then that will give you all tables, their owners and what catalog (database) they are in.
John
February 27, 2007 at 9:12 am
Interesting, your last query returns 4 columns, Table_catalog, Table_Schema, Table_Name, Table_Type, but no row information.
Same results on a test server.
What could I be doing wrong?
R.,
Ed
February 27, 2007 at 9:19 am
Ed
I'm stumped. Try putting USE MyDB at the beginning, where MyDB is the database in question. Otherwise, do you have a case-sensitive collation on your server? If so, did you type the query in upper case like I did?
Try this:
SELECT [name] FROM sysobjects WHERE xtype = 'U'
John
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply