Click here to monitor SSC
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1051 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
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 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
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 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 (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

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



Greg Larsen
Greg Larsen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1051 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1051 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
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3625 Visits: 3110
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

Newbie on www.simple-talk.com
Allen Cui-55137
Allen Cui-55137
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

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



David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3625 Visits: 3110
Thats the baby. Thanks

LinkedIn Profile

Newbie on www.simple-talk.com
jack-64275
jack-64275
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 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