Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2007-04-11 : 06:05:23
|
| Hi,I have a few db's that I am backing up from an old sql2000 system and moving them to the new sql2005 server. The first db's restored without any problems but now I am getting some problems with users and logins.I am restoring in the following order1.) Creating empty db "123"2.) Restoring database from file to database "123"3.) Creating Login to this database (error happens when linking it to the database, "user exists")When connecting thru QA I get "Cannot open user default database login failed"I only have 1 login per database and I don't mind manually deleting and recreating them all but it doesnt seem to be working :SAny suggestions much appreciated!! :)Thanks!mike123 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-11 : 10:07:48
|
| Connect to sql with as sysadmin and run sp_change_users_login to remap sql login to db user. |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2007-04-11 : 14:17:20
|
| ok I will look into doing this.I am still wondering why it was not necessary for my first 2 db's that I restored? Any idea?Thanks!mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-11 : 14:21:01
|
| Step1 isn't needed.Step3 should happen before step2. Here is what I use to transfer my logins. I don't need to unoprhan any users after the restore as the sids are the same. Make sure to change the default database to master prior to running the add login if the default database doesn't exist yet.http://www.sqlmag.com/articles/index.cfm?articleid=16090&Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-11 : 14:48:37
|
| And this issue will not happen on trusted sql logins. |
|
|
|
|
|
|
restoring DB (users/login problems)