Selecting tables from a non dbo query schema

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Selecting tables from a non dbo query schema

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2013-01-25 : 13:46:34

i have some tables in a car schema.

i changed my user's default schema to car

but when i try to do the following:

select * from toyota
i still get the following:

Msg 208, Level 16, State 1, Line 2 Invalid object name 'toyota'.

i have to specify car before it works like so:

select * from car.toyota
i already did this:

ALTER USER mex WITH NAME = mex;

ALTER USER mex WITH DEFAULT_SCHEMA = car;
is there something else i'm missing? how can i keep the car schema but still be able to query it with just the table name?

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2013-01-25 : 14:23:51

Hi,
First check how many schema is mapped with user mex?
If this user is having multiple schema then transfer schema to other user except car.
(Note:If you want mex to access only toyota table)

Use (Database_Name)
GO
SELECT name FROM sys.schemas WHERE principal_id = USER_ID('mex')
--Transfer all schema to other user
Go
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [dbo]

Then only you can access table using below query
'select * from toyota'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-26 : 00:50:05

quote:
Originally posted by xrum

i have some tables in a car schema.

i changed my user's default schema to car

but when i try to do the following:

select * from toyota
i still get the following:

Msg 208, Level 16, State 1, Line 2 Invalid object name 'toyota'.

i have to specify car before it works like so:

select * from car.toyota
i already did this:

ALTER USER mex WITH NAME = mex;

ALTER USER mex WITH DEFAULT_SCHEMA = car;
is there something else i'm missing? how can i keep the car schema but still be able to query it with just the table name?

are you sure you've logged in using mex user itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources