February 21, 2009 at 9:19 am
If I'm moving databases from a 2000 server to a new 2005 server, should I restore the databases, then use sp_help_revlogin to script & import the logins ? Or should the logins exist first ?
What about if I do a test run, then need to restore newer versions of the databases onto the new servers. Will I need to re-import the logins ?
February 21, 2009 at 10:00 am
homebrew01 (2/21/2009)
If I'm moving databases from a 2000 server to a new 2005 server, should I restore the databases, then use sp_help_revlogin to script & import the logins ? Or should the logins exist first ?
If you script the logins from the 2000 server with their SIDs and create them before you restore the database, you won't have the orphaned user problem
What about if I do a test run, then need to restore newer versions of the databases onto the new servers. Will I need to re-import the logins ?
Not unless you rebuild the master database. Logins are stored in master, not in the user database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2009 at 10:28 am
In some cases I already restored the databases, so how do I handle the orphan SID problem ? I can re-restore most of them if that helps, but 1 is too big and I want to test some things tomorrow. I can re-restore the big one next week before go-live.
February 21, 2009 at 11:06 am
Look up "Orphaned users" in Books Online
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2009 at 12:20 pm
if you are using sp_help_revlogin you won't get any orphaned users. Make sure you use the 2000 to 2005 version of sp_help_revlogin.
http://support.microsoft.com/kb/246133
As for databases or logins first, either will work, but I suggest databases first so the default database can be set when sp_help_revlogin script is run in.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply