October 10, 2007 at 3:25 pm
Hi all,
I've been giving google a good workout on this matter, with modest success, and so am hoping that you'll have a few insights to share on how best to proceed -- I'm guessing that the answer is quite simple, and that I simply haven't stumbled across it yet.
I'm tinkering with an application which runs quite happily with SQL 2000 as its back end. Testing it out on 2005, I've encountered a few bumps in the road.
Take a db where half of the tables are owned by dbo, and the rest by a different owner. In 2000, I can happily SELECT * from [tableName] regardless of the owner. In 2005, how can I do a similar select without having to include the prefix? Is there a way to have the db search for an appropriate table within the db when a query is thrown at it, so long as there aren't any naming conflicts with the table names themselves resulting in ambiguity?
I've encountered SYNONYM, and testing reveals that the use of synonyms works around the issue to a degree, although it seems like a bit of a hack that's missed the mark. Also, if I'm running a batch which alters multiple tables and falls to using a synonym, it will fail due to the limitations of synonyms, of course.
Ideally, the end result would allow use of the tables through either of [owner].[tableName] or just [tableName].
I look forward to hearing from you,
- Ian
October 11, 2007 at 2:30 am
Ian,
the best solution would be to always use the two-part name like dbo.tablename. Not only is it a best practice but there's also a (very) small performance gain by doing so.
But if that's not an option than you need to make sure that your users have the correct default schema. If you address an object without the schema name SQL Server will first check the sys schema if such an object exists, when the users default schema and if it still hasn't found the object it will try the dbo schema.
You can read more about schemas in BOL or on this site.
Markus
[font="Verdana"]Markus Bohse[/font]
October 11, 2007 at 10:21 am
Thank you very much Markus 🙂
I'm using synonyms in testing at the moment to buy me a couple of days -- ideally enough time to learn how to implement a more appropriate solution with schemas through BOL, etc.
October 12, 2007 at 7:55 am
I would wonder why objects are owned by different users. My practice (and I was told it was best practice??) was that dbo should own all objects in production. Because issues like this, restores ... can all get hairy when objects are owned by different schemas. If there is not a good reason for different owners, I would use sp_changeobjectowner (for 2000 - run before migration http://msdn2.microsoft.com/en-us/library/ms177519.aspx ) or ALTER AUTHORIZATION (for 2005 if you do it post migration).
Good luck
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply