SQL Server Performance Forum – Threads Archive
Table not recognizedI have a simple query (Select * from tblTest) but it says that tblTest is an invalid object. But I can see it, edit it, and add data to it through Enterprise Manager? Note: I added the tables using the "Update Database" in Visio Any suggestions will be helpfull. Thanks,
How about SQL Analyzer?.
I added the tables through Visio, then I did my select in Query Analyzer.
How about table permissions?
How about table ownership? If you are not dbo and the table is or the table is not dbo and you are then QA it wont find the table.
In EM it displays all tables (and includes their owner) Chris
the owner is not dbo, it is actually the SQL User I assigned to Visio. Do I have to change the ownership of the table?
Yes you have to change owneship or login to Query Analyzer with table user.
Hi, Assuming the owner is named "visio", then you can reference the table using below query: select * from visio.tblTest Or you may want to change the owner of the table using sp_changeobjectowner system sp. That is,
sp_changeobjectowner [ @objname = ] ‘object’ , [ @newowner = ] ‘owner’ Jon M
Do I have to run that sp for each table, or is there a way to change ownership for all tables in a database?
Hi, Check this out:
http://support.microsoft.com/default.aspx?kbid=275312 Jon M Forgot to mention: you may need to retrieve only those xtype=’U’ and type=’U’ (user tables) in sysobjects table. For details see BOL re: sysobjects table. But the link above should give you an idea how you can change the ownership to ease the process.
If you want to keep the owner visio and have a reason for it, then keep it. If you dont have a reason, then I’d set it back to dbo. Its more work to maintain them and of course make sure that anyone else who would ever use them knows to always alias. I worked with a db one time where the developers didnt know the concept of table ownership and there were 3 tables with the same name, but different owners. They couldnt understand why they could all run the same query and get different results. Chris