June 26, 2007 at 10:25 am
I've done some testing with a new Sql2k5 Dev Server. I used the detach/attach method to migrate the databases. Of course, like others have complained, I've had some login/permission problems. I do know about sp_help_resolve_logins and the other related sp's. My question is: It seemed to me that I had less issues with logins when I moved all of the logins first, and then copied and reattached the databases. I haven't seen any info/article that confirmed that this was the preferred order in the migration process. Any thoughts?
thanks,
Dave
July 5, 2007 at 11:49 pm
You can use the scriprt in this link to scrpt all logins
http://support.microsoft.com/kb/246133/
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 10, 2007 at 8:29 am
Hi David,
I would recommend moving logins first. The sp_revlogin script in the link Sugesh provided is a wonderful way of doing that. Also, you can check that the SIDs, passwords, etc. for the logins are looking good before you do the migration. This reduces down time for the DB migration. Here are some caveats of the sp_revlogins script that I have run into before:
1) Any server roles associated with the logins aren't moved (like sysadmin, etc.)
2) Default databases for logins aren't moved either. If the database that is default doesn't exist yet on the server, this is not possible anyhow. For this, I would recommend writing a script to set the default DBs ahead of the move to run after the DBs are moved.
I hope that helps.
Thanks,
Eric
July 10, 2007 at 9:25 am
Eric,
I appreciate your input.
Thank you!
July 10, 2007 at 9:26 am
Sugesh,
The link was very helpful.
Thank you!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply