not able to run the select query user schema objects in sqlserver 2008

Question

Post reply

not able to run the select query user schema objects in sqlserver 2008

balaji.ganga

SSCrazy

Points: 2280
More actions
October 18, 2010 at 9:46 pm

#228197

Hi All,
Our Scenario is we can upgrade from sql 2000 to 2008 server.
I restore sqlserver 2000 db's to sqlserver 2008 express edition client tools.Then i change the
compatablilty mode.
I used to select * from sysobjects query..
Some of the objects comes under dbo schema like Sample Windows credentials and some of the objects comes under balaji schema..
dbo schema is associated with sample login and sample user. when i ran the select query using dbo schema objects. Its runs successfully.
In Sql 2000
login : sample
Password : sample
then i ran the select * from samples, its runs successfully
In sql 2000
login : test
password test
i want to run the sample table here. This table is associted with sample schema and sample login
select * from [sample].[samples]
then its run successfully
But in 2008
login : sample
password : sample
after login the sample name. then i tried to exeute the samples table here
select * from samples
its shows Invalid object name.
then i tried
select * from [sample].[samples]
afterwards its been executed,
An exisiting server (sql 2000). its runs successfully. with out using the schema name
but sql 2008. i cannot able to run the select query with the sample login name..
Can you pls advise me.. where is the problem occurs.. how can i fix it?
Thanks
Balaji.G
Regards
Balaji.G

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Geoff A SSChampion Points: 11511 More actions · Answer 1

maybe you should change the ownership back to dbo.

EXEC sp_changeobjectowner 'TableName', 'dbo'

winash SSChampion Points: 11446 More actions · Answer 2

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.

balaji.ganga SSCrazy Points: 2280 More actions · Answer 3

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

winash SSChampion Points: 11446 More actions · Answer 4

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