February 19, 2007 at 9:08 am
I am tasked to move a database from sql 2000 to sql 2005. Though my research suggests that I can backup a sql 2000 db from enterprise manager and restore it with sql 2005 management studio.
what about logins? how do you transfer those?
February 19, 2007 at 9:18 am
I believe you can use a transfer logins task in DTS/SSIS to move the logins over. Then you probably would need to remap them to the database users with sp_change_users_login.
February 20, 2007 at 4:47 am
Use sp_help_revlogin, see also http://www.sqlservercentral.com/columnists/glarsen/migratingloginstoanotherserver.asp.
It works for sql2k to sql2005 as far as I know.
February 20, 2007 at 6:42 am
Actually Backup and Restore moves logins too it moves everything and you can keep 2000 compatibility level, known issues orphaned permissions solution delete the restore and create a new one. Hope this helps.
Kind regards,
Gift Peddie
February 20, 2007 at 12:04 pm
When I upgraded...
used sp_help_revlogin for Logins, it worked great.
I did the database restore with the move option to setup the database and bring over the authorized users. I think that the 2005 restore process does this, but I ran the sp_change_users_login for all of my logins.
Dont forget about nightly jobs, back-up devices, link servers, etc.
It went very smoothly for me...
Eric
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply