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 |
|
wrightgj
Starting Member
8 Posts |
 Posted - 2011-05-26 : 08:46:56
|
| Hi all,I have a backup / restore procedure that works fine for windows 2000/sql2000, but fails on win 7/sql2008.The problem is when I am restoring the MASTER db from a backup (between the backup and restore, the machine has been rebuilt from the OS upwards, so new OS and new SQL instance).The RESTORE completes successfully, but once complete, the windows authentication logins stop working. This means that the restore of the rest of the tables fails because we connect as the OS administrator user to perform the remaining steps.I've read a snippet somewhere about the SID for the users may have changed and is what is causing the problem, but I cannot find any definitive answers.Please if anyone has had similar issues and/or silutions, I would be most grateful.Graham. |
|
|
raghuveer125
Constraint Violating Yak Guru
285 Posts |
Posted - 2011-05-26 : 09:49:54
|
| Check where you took your master database that system was domain or workgroup member(Even check windows authentication logins names) and then Check where you restored your master database the system is in correct domain or workgroup.(Compare this system logins with your previous windows logins where you took master database).In Love... With Me! |
 |
|
|
wrightgj
Starting Member
8 Posts |
Posted - 2011-05-26 : 10:17:13
|
| Hi,It is the exact same machine, with an exact same automated build applied to it from Backup through to restore.I think it's due to the fact the SID has changed on the windows users & groups (due to the re-install?).Is there an easy way to fix these up or do I need to dump the SIDs from the master database before restoring, then update them all afterwards? |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-05-27 : 10:50:33
|
| You could script out the Logins to produce the proper DROP LOGIN and CREATE LOGIN statements and then run that script. This can done manually via Management Studio but all the information you need to generate the scripts is in the sys.server_principals and sys.sql_logins tables.=======================================Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986) |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-05-27 : 16:19:38
|
| Why would you need to restore the master database. If this is just to get the logins, those can be scripted out and the script rerun as needed. Restoring master should only be done when you have a system crash and need to rebuild the system.Jeff |
|
|
|
wrightgj
Starting Member
8 Posts |
Posted - 2011-06-08 : 09:55:53
|
| Jeff,That's exactly what the script is for... recovering the DB after a HDD failure/crash etc.As soon as MASTER is restored (the first step) bam, the whole SQL instance is locked out and the script is toasted. |
|
|
|
wrightgj
Starting Member
8 Posts |
Posted - 2011-06-09 : 11:36:52
|
| Ok. Solved it.Although the logins were locked out when trying to connect normally, when connected in Single user mode, SQLCMD could connect okay.Therefore after the restore, I restarted SQL in single user mode and used SQLCMD to run a script to remove all the logins and re-add them.This fixed-up the SIDs and all is now working great. |
|
|
|
|
|
|
|
|
Restoring valid MASTER backup ruins login