October 19, 2010 at 7:44 am
maybe you should change the ownership back to dbo.
EXEC sp_changeobjectowner 'TableName', 'dbo'
October 19, 2010 at 8:01 am
Maybe you need to set the default schema of the user - the user seems to be mapped to the "dbo" schema after your upgrade?
From this link:
A query such as "select * from table1" in SQL Server 2000 goes through a set of steps to resolve and validate the object references before execution. The search first looks at the identity of the connection executing the query. Let’s assume we have a user connection through "user1". For “select * from table1”, SQL Server 2000 searches for the object "user1.table1" first. If object does not exist, the next step is to search for "dbo.table1".
...
Reusing the example from above; with DEFAULT_SCHEMA set to 'dbo' in a SQL Server 2005 database, user1 executing 'select * from table1' will directly resolve immediately to 'dbo.table1' without searching for 'user1.table1'.
[edit]:
Maybe this isn't the reason. From the same article:
In fact, if you use the new CREATE USER syntax from SQL server 2005, you will get ‘dbo’ as the DEFAULT_SCHEMA by default. So if you are developing in a new database under SQL Server 2005, you are already taking advantage of this name resolution shortcut. However, the behavior for upgraded databases is different . During upgrade from previous versions of SQL Server, to preserve the same behavior, SQL Server 2005 sets the DEFAULT_SCHEMA to the name of the user. Example: ‘user1’ gets DEFAULT_SCHEMA of 'user1' when you upgrade a database from an earlier version to SQL Server 2005 by attaching or any other method.
The upgrade should have ideally taken care of this and it looks like you shouldn't be facing this issue. Looks like what Geoff recommended is more valid.
October 21, 2010 at 7:26 am
Hi Winash,
Thanks for your reply...
As per Geoff instructions, if i changed the objects owner from user schema to dbo schema
its working fine. but an existing server runs at succcessfully using user schema.
Say for example :
in sql 2000:
database : sample
user login : balaji, password jrdba#21
when i used this credentials in sql 2000 server. its ran all objects successfully
and if i used some other credentials.. i need to be specified at
use sample
select * from [balaji].[tablename] like this
i had apply the same procedure in sql 2008.
database : sample
user login : balaji.
password : jrdba#21
if i ran any objects using select query.. it will not run..
Error is " Invalid object Name"...
But in sql 2000. it will work.
I cannot change the owner of a tables. because an application will call the db objects like this
userschema.objectname..
Can you pls help me
Regards
Balaji.G
October 21, 2010 at 1:04 pm
In your new SQL 2008 environment - did you try changing the default schema of your user login and see if that helps?
Something like this:
ALTER USER balaji WITH DEFAULT_SCHEMA=balaji
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply