SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Selecting tables from a non dbo query schema
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xrum
Yak Posting Veteran

87 Posts

Posted - 01/25/2013 :  13:46:34  Show Profile  Reply with Quote
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

India
285 Posts

Posted - 01/25/2013 :  14:23:51  Show Profile  Reply with Quote
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'.




Edited by - raghuveer125 on 01/25/2013 14:32:21
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/26/2013 :  00:50:05  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000