Microsoft SQL Server 2000 Query Analyzer Question

I have a database that is hosted by a Microsoft 2000 SQL Server. I'm running into a strange problem that I'm not sure how to fix or how it started.

For the sake of this question, the database is named "MyDatabase". There is a SQL user named "MyUser".

When I launch Query Analyzer, I will login as this user (MyUser) and connect to the MyDatabase. The problem I started having is that anytime I perform a simple query on table I have to supply the database name in front of the table.

So for example, this used to work:
SELECT * FROM tableName;

Now I have to do it this way:
SELECT * FROM MyDatabase.tableName;

If I do it without the database name supplied like the first example, I will get this error:
"Invalid object tableName".

I used to NOT have to do this. I believe something is changed. Can someone please help me understand how to switch this back?

Thank you.
greddinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AanvikCommented:
Try this (Use dbo schema)
SELECT * FROM MyDatabase.dbo.tableName;
0
greddinAuthor Commented:
Ok I ran that but it gives this error:

"Invalid object name MyDatabase.dbo.tableName"

Again, I don't want to have to supply this. How can I change it back so that I don't have to supply the extra information?

Thanks
0
Cedric_DCommented:
try delete all user schemas, and recreate first SQL Login "MyUser" and then attach this login to database.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Cedric_DCommented:
first try delete all users and login under sa.

BTW which database you see at Query Analyser's combobox at the top?
0
greddinAuthor Commented:
Can you provide the step for me to delete all user schemas?

Can I do this from the Enterprise Manager?
0
greddinAuthor Commented:
The database I see at the top is "MyDatabase".

I don't have the sa password. I've have to ask someone for that.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
When you use a two-part name, the first part is ALWAYS the schema (user) name. The full qualified name of an object is

myServer.MyDatabase.MyUser.MyTable

and has to be used from right to left (only all left parts can be omitted, this is).

A "select * from MyDatabase.MyTable" does tell us the table was created with a user name like the database name (which is possible if the user exists). Because this user is not your user, and it is not dbo, you cannot access it without using the schema name.

However, you can use sp_changeobjectowner 'MyDatabase.tableName', 'MyUser'
to change it back.
0
greddinAuthor Commented:
My database name and user name are both the same. I've used sample names above, but the reals names are:

Database name: db_applicaiton1
User: db_application1

My User and Db are both the same name.

Does this have any effect?
0
greddinAuthor Commented:
In Enterprise Manager, all the tables of this particular database do show the owner as "db_application1". Isn't this correct?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This is strange, indeed.
0
greddinAuthor Commented:
If I want to change the owner to dbo? How do I do this?

I assume I need to login as sa right? Then run a system procedure?
0
Cedric_DCommented:
You not answered, do you set correct Database at combo on the top of Query Analyser's?
Maybe, simply default db of that user was changed?
0
greddinAuthor Commented:
Sorry, I though I answer. The database is correctly selected when I login to Query Analyzer with my user. It's not master, etc.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Using sa will be best, then (as I wrote earlier) use
sp_changeobjectowner 'MyDatabase.tableName', 'MyUser'
0
greddinAuthor Commented:
Ok, I was able to get the dbo user info.

I changed ownership of one of the tables of this database as a test.

I ran sp_changeobjectowner 'db_application1.Config', 'dbo'

I logged into Query Analyzer (as sa) and was able to run the query on this table without the added info.

SELECT * FROM Config

Worked ok.

Question:
How do I change ownership of all the tables and database to the dbo?

Thanks
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This works for both MSSQL 2000 and 2005:

set implicit_transactions off;	/* = autocommit on */
 
declare @touser sysname;
declare @fromuser sysname;
set @fromuser='db_application1';
set @touser='dbo';
 
declare csr cursor for select @fromuser+'.'+name from sysobjects where uid = user_id(@fromuser) and type in ('U', 'V') and name not like 'sys%' for read only;
declare @tbl varchar(32);
declare @sel nvarchar(4000);
open csr;
fetch csr into @tbl
while @@fetch_status = 0
begin
   print  @tbl;
   set @sel=N'alter schema '+@touser+' transfer '+ @tbl;
   if 2000 = (select 2000 where @@version like '%8.00.%')
		exec sp_changeobjectowner @tbl, @touser
   else
		exec sp_executesql @sel
   fetch csr into @tbl
end
deallocate csr
go

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
greddinAuthor Commented:
Thanks for all your help. It's working good now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.