Migrating Logins To Another Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Gregory Larsen » Migrating Logins To Another Server

13 posts, Page 1 of 2

1 2 »»

Migrating Logins To Another Server

Rate Topic

Display Mode

Topic Options

Author

Message

Greg Larsen

Posted Sunday, September 28, 2003 12:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/glarsen/migratingloginstoanotherserver.asp

Gregory A. Larsen, MVP

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

Post #16740

StefanJ

Posted Tuesday, September 30, 2003 3:38 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 7:44 AM
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?

Post #81248

John Campbell-96530

Posted Tuesday, September 30, 2003 6:03 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, January 23, 2007 4:46 AM
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 :>)

Post #81249

Rabenklau

Posted Tuesday, September 30, 2003 7:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 16, 2003 12:00 AM
Points: 3, Visits: 1

It does not transfer the default database though.

Post #81250

Greg Larsen

Posted Tuesday, September 30, 2003 8:51 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255

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

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

Post #81251

Greg Larsen

Posted Tuesday, September 30, 2003 9:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255

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

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

Post #81252

David.Poole

Posted Tuesday, September 30, 2003 9:39 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 2,749, Visits: 1,407

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

Post #81253