Solved

Problems moving from from old MSSQL2008 server to new MSSQL2008 database server

Posted on 2013-11-02
4
269 Views
Last Modified: 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
0
Comment
Question by:Jamie
  • 2
  • 2
4 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39618793
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.
0
 

Author Comment

by:Jamie
ID: 39618827
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
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 39618862
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'
0
 

Author Closing Comment

by:Jamie
ID: 39618874
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
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question