asked on

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

R_Hos

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

ichikuma

ASKER

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

Crag

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.

Crag

If they doesn't help, check the owner of the schema - that may tie the two together.

ichikuma

ASKER

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

Crag

You can use the following two queries to check:

select *
from information_schema.schemata;

select distinct table_schema
from information_schema.tables;

ichikuma

ASKER

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

Crag

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.

ichikuma

ASKER

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 :-(.

Crag

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'

ichikuma

ASKER

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.

Crag

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.

ichikuma

ASKER

Oh, sorry, I misread it. Yes, the table is there:

mydb myuser Users BASE TABLE

ichikuma

ASKER

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

Crag

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

ichikuma

ASKER

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

ASKER CERTIFIED SOLUTION

Crag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ichikuma

ASKER

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

Crag

Good news!