December 10, 2010 at 7:49 am
I restored a sql server 2000 database to a sql server 2008R2 instance and it went throught the upgrades that sql server 2008 R2 automatically applies.
All the tables and procs in this database were under a schema called Forum, which is owned by a user called Forum.
In sql server 2000 when i used to connect with user Forum i didn't have to use the schema name prefix in order to use any table or procedure. e.g. exec myproc would work fine. BUT now in sql server 2008 R2 this is not working, i have to explicitly specify the schema name with the objects even though i am connected with the Forum user who is the owner of the Forum schema. e.g. exec Forum.myproc
What am i doing wrong?
December 10, 2010 at 8:22 am
I'll start by checking "Forum" user default schema.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 10, 2010 at 8:26 am
Thanks for the response Paul.
That's exactly what i checked and it was right, defualt schema is Forum with db owner permissions.
I wonder some how during the upgrade process it has lost the object ownership mapping and it does not know any more that these objects are owned by Forum schema (although they are appearing with Forum. prefix in the tables and procedures list).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply