• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Restoring SQL Server 2000 db to SQL Server 2005 db user issues.

I'm currently trying to migrate a SQL Server 2000 database to a SQL Server 2005 database.  The way I used to do it was:
1.  Backup the source db to a file.
2.  Restore the source db to my destination server.
3.  Change the ower of the objects (tables, stored procedures, etc) to the dbo user.
4.  Drop my db user.
5.  Recreate the db user.
6.  Assign the user to db_Reader, db_Writer and db_Owner.
7.  Change the owner of the objects back to my new db user.

On SQL Server 2000 this process worked fine.  I did the same process for my SQL Server 2005 database and I can't run queries without qualifying it with my db user.  select * from users used to work on SQL 2000 but now I have to do select * from myuser.users for SQL 2005.

I'm assuming I missed some kind of security setting for my db user.  Can someone advise on where I should start looking?

Thanks for any help,

Craig
0
ichikuma
Asked:
ichikuma
  • 9
  • 9
1 Solution
 
R_HosCommented:
assuming that your db user will only be used to access 1 database, you may have the 'default database' set incorrectly (it defaults to 'master').  If you set it to your myuser database all the queries that user runs should default the selected database and you shouldn't have to declare which DB you are trying to use.

~R
0
 
ichikumaAuthor Commented:
Thanks for the quick response.  When I created the user, I set the default db to the correct database.  I just looked at the properties of myuser and it's still pointing to my database.

Anything else I can look at?

Thanks again,

Craig
0
 
CragCommented:
It would appear that you have set the schemas in the database differently to the name of the database user.
Have you looked at the Default Schema parameter for the Database User?
SQL 2000 had schema/users combined whereas SQL 2005 they are two different things.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
CragCommented:
If they doesn't help, check the owner of the schema - that may tie the two together.
0
 
ichikumaAuthor Commented:
Hi Crag,

Thanks for the suggestions, I've done the following and still can't get it to work:

USE mydb
ALTER AUTHORIZATION ON SCHEMA::myuser TO myuser

USE mydb;
ALTER USER myuser WITH DEFAULT_SCHEMA = mydb;
GO

I also tried:
use mydb
ALTER AUTHORIZATION ON SCHEMA::mydb TO myuser

and got the following error:
Msg 15151, Level 16, State 1, Line 2
Cannot find the schema 'mydb', because it does not exist or you do not have permission.

Maybe that is a clue, could the db not have a schema?

Thanks again for your help,

Craig
0
 
CragCommented:
You can use the following two queries to check:



select *
from information_schema.schemata;

select distinct table_schema
from information_schema.tables;
0
 
ichikumaAuthor Commented:
I ran those two queries against my db and found something interesting.  The first query, returned a bunch of rows but the most interesting was:
catalogue_name      schema_name     schema_owner
mydb                        myuser                dbo
mydb                        dbo                      dbo

The second query returned:
dbo
myuser

So, correct me if I'm wrong, it looks like dbo owns the myuser schema.  Is there a query for me to change it?  Do you think that is the issue?

Thanks again,

Craig
0
 
CragCommented:
it shouldn't be an issue as the user also owns the database and should therefore have access to it.
It've tried to replicate the issue and not been able to so not sure why it doesn't work.
0
 
ichikumaAuthor Commented:
I ran this query again:
ALTER AUTHORIZATION ON SCHEMA::myuser TO myuser

so now the first query returns what I think it should:
catalogue_name      schema_name     schema_owner
mydb                        myuser                myuser
mydb                        dbo                      dbo

But, I still can't run a query without putting the myuser in front :-(.
0
 
CragCommented:
Can you run the following and post the output?
Swap users for the name of your table

select *
from information_schema.tables
where table_name ='users'
0
 
ichikumaAuthor Commented:
If I run it replacing myuser for users, it doesn't return anything.  If I run it and change table_schema = 'myuser' then I get back all of the tables in my db.

Does that help?  Sorry I'm bogging you down with this, I've been working on it for 2 days and have gotten nowhere with it.  Thanks so much for your help.
0
 
CragCommented:
Do you have a table called users, as per the example at the top?
Of course using the table_schema version works - what I wanted to see was one row for a specific table that you have been trying to access.
If it doesn't return anything then you don't have a table called users in this database.
0
 
ichikumaAuthor Commented:
Oh, sorry, I misread it.  Yes, the table is there:

mydb     myuser   Users      BASE TABLE

0
 
ichikumaAuthor Commented:
Crag,

I think I figured it out, well, at least I got it to work.  When I restored my original db, the owner of the tables, stored procs, etc were owned by myuser.  I was messing around and changed the user tables to be owned by dbo.  Then it worked!!!  I changed all of my objects to be owned by dbo and they are working as well.

Just for my knowledge, do you know why it would work with dbo owning the objects and not myuser?

Thanks again for all of your help, I really appreciate it,

Craig
0
 
CragCommented:
probably because myuser is aliased to dbo which means that it automatically puts dbo in front of the tables when you want to query them. This would also make sense when you want to see the tables prefixed by myuser.
Use the following command to check your myuser login in the database:

sp_helpuser myUser
0
 
ichikumaAuthor Commented:
When I run that sp, I get the following results:

UserName    GroupName       LoginName   DefDBName  DefSchemaName UserID  
myUser        db_owner           myUser        myDb           dbo                       5  
myUser        db_datareader    myUser        myDb            dbo                       5  
myUser        db_datawriter     myUser        myDb            dbo                       5  

Now, I have another problem.  Some of the code calls sp's like myUser.spName.  Well, now all of the objects have dbo as the owner so it can't find the sp's.  Man, who knew it was going to be this hard?

Thanks again,

Craig
0
 
CragCommented:
I'm going to guess that the fastest way to resolve this is to change the owner of the objects back to your account so that the SPs work ok.
I might have to try this so it may take a while to set up a test.
0
 
ichikumaAuthor Commented:
Hi Crag,

I got it.  It seems there is an order to things.  I can now run the sp's either way.  I don't have time to write it up but I will in case someone else runs into this issue.

Thanks again for all of your help,

Craig
0
 
CragCommented:
Good news!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now