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,