Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Microsoft SQL Server 2000 Query Analyzer Question

Posted on 2008-11-05
17
Medium Priority
?
266 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:greddin
  • 9
  • 4
  • 3
  • +1
17 Comments
 
LVL 5

Expert Comment

by:Aanvik
ID: 22888151
Try this (Use dbo schema)
SELECT * FROM MyDatabase.dbo.tableName;
0
 

Author Comment

by:greddin
ID: 22888204
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
 
LVL 7

Expert Comment

by:Cedric_D
ID: 22888243
try delete all user schemas, and recreate first SQL Login "MyUser" and then attach this login to database.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 7

Expert Comment

by:Cedric_D
ID: 22888257
first try delete all users and login under sa.

BTW which database you see at Query Analyser's combobox at the top?
0
 

Author Comment

by:greddin
ID: 22888264
Can you provide the step for me to delete all user schemas?

Can I do this from the Enterprise Manager?
0
 

Author Comment

by:greddin
ID: 22888276
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
 
LVL 72

Expert Comment

by:Qlemo
ID: 22888409
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
 

Author Comment

by:greddin
ID: 22888459
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
 

Author Comment

by:greddin
ID: 22888480
In Enterprise Manager, all the tables of this particular database do show the owner as "db_application1". Isn't this correct?
0
 
LVL 72

Expert Comment

by:Qlemo
ID: 22888615
This is strange, indeed.
0
 

Author Comment

by:greddin
ID: 22888653
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
 
LVL 7

Expert Comment

by:Cedric_D
ID: 22888818
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
 

Author Comment

by:greddin
ID: 22888892
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
 
LVL 72

Expert Comment

by:Qlemo
ID: 22889138
Using sa will be best, then (as I wrote earlier) use
sp_changeobjectowner 'MyDatabase.tableName', 'MyUser'
0
 

Author Comment

by:greddin
ID: 22889308
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
 
LVL 72

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 22889479
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
 

Author Closing Comment

by:greddin
ID: 31513609
Thanks for all your help. It's working good now.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question