SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Migrating Logins To Another Server


Migrating Logins To Another Server

Author
Message
Greg Larsen
Greg Larsen
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4155 Visits: 290
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/glarsen/migratingloginstoanotherserver.asp

Gregory A. Larsen, MVP
StefanJ
StefanJ
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 11
I have experienced problems transferring logins so this article will be very useful if I have to do it in a live situation. Just one clarification.

Would it be necessary to run this script if I restored backups to a completely new server. I imagine the order would be to transfer the system databases first followed by the user databases. Would the logins and users be correctly linked after the restore?



John Campbell-96530
John Campbell-96530
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 1
I just use the script to transfer the logins from a DEC ALPHA SQL 7 server to a new Intel SQL 2000 server with no problems. I was unable to use the Transfer Logins DTS job on this combination. Timely ... Absolutely :>Wink



Rabenklau
Rabenklau
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 1
It does not transfer the default database though.



Greg Larsen
Greg Larsen
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4155 Visits: 290
When you transfer master all the logins will come across so you would not have to run this script. This script is used to copy logins and passwords without restore the master database.

Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here:http:www.sqlservercentral.com/bestof/purchase.asp

Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP
Greg Larsen
Greg Larsen
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4155 Visits: 290
I agree it would be nice to transfer the default database. As the script comes from Microsoft it does not set the default database, and that is what I presented in the article.

Normally if I am only moving one database I modify the sp_help_revlogin SP to only select logins if they are associated with the database I am moving and I add "execute master..sp_defaultdb " commands to the SP to make sure the default db get set.

It could also be modified to determine the default database and set it appropriately. This would be a good enhancement to this script.

Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here:http:www.sqlservercentral.com/bestof/purchase.asp

Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16236 Visits: 3403
Can anyone remember the SP that fixes orphaned users?

I seem to remember that if the login name and user name were the same it assigns a matching SID.

LinkedIn Profile
www.simple-talk.com
Allen Cui-55137
Allen Cui-55137
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8571 Visits: 1
Is sp_change_users_login you are looking for?



Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16236 Visits: 3403
Thats the baby. Thanks

LinkedIn Profile
www.simple-talk.com
jack-64275
jack-64275
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 559
This would be a lot better if it was fit to screen size rather than having to scroll back and forth to read it...



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search