Problems moving from from old MSSQL2008 server to new MSSQL2008 database server
Hello Experts,
I'm in the process of migrating our MSSQL2008 database from our old (web) server to our new (web) server, by the following method;
1. Backed up MSSQL2008 database on old server - used MSSQL database backup
2. Copied file from old server to new server
3. Created a new MQL20008 database on the new server - called it the same as the old database (MyDBase)
4. Restored the database onto the new server, using MSSQL restore - all tables and data created correctly and appear in MyDBase database on new server.
5. My user login name appears in new database (JamWales), but I cannot log into the database?! I've tried;
a) resetting the password
b) setting the users default database to master
But nothing I try works - where am I going wrong?!? It is driving me nuts!!!
I'm getting the attached error and also;
Cannot open database "MyDBase" requested by the login. The login failed.
Login failed for user 'jamwales'.
Help!
Best Regards
JamWales
MSSQL2008Error.png
I'm in the process of migrating our MSSQL2008 database from our old (web) server to our new (web) server, by the following method;
1. Backed up MSSQL2008 database on old server - used MSSQL database backup
2. Copied file from old server to new server
3. Created a new MQL20008 database on the new server - called it the same as the old database (MyDBase)
4. Restored the database onto the new server, using MSSQL restore - all tables and data created correctly and appear in MyDBase database on new server.
5. My user login name appears in new database (JamWales), but I cannot log into the database?! I've tried;
a) resetting the password
b) setting the users default database to master
But nothing I try works - where am I going wrong?!? It is driving me nuts!!!
I'm getting the attached error and also;
Cannot open database "MyDBase" requested by the login. The login failed.
Login failed for user 'jamwales'.
Help!
Best Regards
JamWales
MSSQL2008Error.png
ASKER
Hi Jack72,
Thank you for your reply - it is an sql account. Yes, orphaned sounds right, but when I delete the server login from the database and re-create it, it will not let me "user mapping" it, because of the attached error above.
When I also tried to delete the orphaned database account from the database users, it says the account is being referenced by a schema - and that the database principal owns a schema in the database?
I wasn't sure if there was a script I could run to sort all this out?
Regards
Jamie
Thank you for your reply - it is an sql account. Yes, orphaned sounds right, but when I delete the server login from the database and re-create it, it will not let me "user mapping" it, because of the attached error above.
When I also tried to delete the orphaned database account from the database users, it says the account is being referenced by a schema - and that the database principal owns a schema in the database?
I wasn't sure if there was a script I could run to sort all this out?
Regards
Jamie
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Jacko72,
Many thanks for your help in sorting this out - found the following solution also.
http://msdn.microsoft.com/en-us/library/ms175475.aspx
All working now! :-)
Regards
JamWales
Many thanks for your help in sorting this out - found the following solution also.
http://msdn.microsoft.com/en-us/library/ms175475.aspx
All working now! :-)
Regards
JamWales
If you check in the server logins rather than the database logins it is likely that this account doesn't exist unless you have added it to the new server. Try removing it from the database, then add it to the server logins if not already added then add it back to the database again.