Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Migrating Logins To Another Server Expand / Collapse
Author
Message
Posted Sunday, September 28, 2003 12:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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
Posted Tuesday, September 30, 2003 3:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Tuesday, September 30, 2003 6:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Tuesday, September 30, 2003 7:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, September 30, 2003 8:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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
Posted Tuesday, September 30, 2003 9:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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
Posted Tuesday, September 30, 2003 9:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:15 AM
Points: 2,889, Visits: 1,778
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
Post #81253
Posted Tuesday, September 30, 2003 10:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 2, 2005 11:49 AM
Points: 2,611, Visits: 1
Is sp_change_users_login you are looking for?





Post #81254
Posted Wednesday, October 1, 2003 1:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:15 AM
Points: 2,889, Visits: 1,778
Thats the baby. Thanks



LinkedIn Profile
Newbie on www.simple-talk.com
Post #81255
Posted Thursday, September 30, 2004 6:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 16, 2011 11:21 AM
Points: 169, 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...


Post #139424
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse