What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!
Course Of The Month
8 days, left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Problems moving from from old MSSQL2008 server to new MSSQL2008 database server
Posted on 2013-11-02
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
4 Comments
The user in the database will be an orphaned account from the old server, is it a sql or domain account?.
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.
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.
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
ok if you run the script below it should sort it out, make sure it is run against the database that has been restored :
Use restoredDatabase
Go
ALTER USER user WITH LOGIN = serverlogin
Go
where user is the database user, and serverlogin is the user account in the server logins which will be the same in your case I believe both 'jamwales'
Use restoredDatabase
Go
ALTER USER user WITH LOGIN = serverlogin
Go
where user is the database user, and serverlogin is the user account in the server logins which will be the same in your case I believe both 'jamwales'
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:
Each Login (Windows or SQL)
* Its Server Roles
* Every database to which the login is mapped
* The associated "Database User" for this …
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you!
In this Micro Tutorial, you'll learn yo…
- Microsoft Applications
- Windows 10
- Windows OS
- Fonts Typography
- Windows 7, Microsoft Word
Suggested Courses
Course of the Month8 days, left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.