Skip to content

TechRepublic

  • Top Products
  • AI
  • Developer
  • Payroll
  • Security
  • Project Management
  • Accounting
  • CRM
  • Academy
Resources
  • TechRepublic Premium
  • TechRepublic Academy
  • Newsletters
  • Resource Library
  • Forums
  • Sponsored
Go Premium
Popular Topics
  • Top Products
  • AI
  • Developer
  • Payroll
  • Security
  • Project Management
  • Accounting
  • CRM
  • Academy
  • Project Management
  • Innovation
  • Cheat Sheets
  • Big Data
  • Tech Jobs
View All Topics
Go Premium
Data Management

How do I… Transfer logins from one SQL Server 2005 instance to another SQL Server 2005 instance?

By Steven S. Warren September 22, 2007, 11:54 AM PDT

Image
1
of 2

How do I… Transfer logins from one SQL Server 2005 instance to another SQL Server 2005 instance?

By Steven Warren

By Steven Warren

This gallery is also available as a TechRepublic blog post and download.

In my last How do I…, I discussed how to restore a SQL Server to a new server. After the article published, I had some questions on the best way to move SQL Server logins from one SQL Server 2005 instance to another SQL Server 2005 instance.

I want to preface that you can move logins from one SQL Server to another SQL Server in many different ways. In my experience, I have found that this is the easiest way to move logins from one SQL Server to another SQL Server while keep existing passwords.

We will begin by downloading a script called Sp_Help_RevLogin.

Note: If you are transferring logins from SQL Server 2000 to SQL Server 2005, download this script.

Next, navigate to Start | All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio. Simply highlight and copy the script from the link provided and paste into SQL Server Management Studio as shown.

Note: When you first open SQL Server Management Studio, you must click New Query, and select the Master database prior to pasting the script and clicking Execute.

How do I… Transfer logins from one SQL Server 2005 instance to another SQL Server 2005 instance?

Move SQL Server logins

Move SQL Server logins

We still have the final task of resolving permissions. When you move a database from one server to another server that is running SQL Server, a mismatch happens between the SIDs (security identifier numbers). The sp_change_users_login can fix the issue.

Open SQL Server Management studio and run the script as shown. The accompanying image displays how Listing B will look in SQL Server Management Studio.

Listing B
DECLARE @UserName nvarchar(255)
DECLARE Cursor_OrphanedUser cursor for
SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name
OPEN Cursor_OrphanedUser
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName ‘ Synchronization of Logins in Progress’
EXEC sp_change_users_login ‘Update_one’, @UserName, @UserName
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
END
CLOSE Cursor_OrphanedUser
DEALLOCATE Cursor_OrphanedUser
Go

Run this script on each database for which you have to synchronize logins. Once you have finished running this script on each database, you have successfully moved SQL Server logins from one server to another.

  • Data Management
  • Account Information

    Share with Your Friends

    How do I… Transfer logins from one SQL Server 2005 instance to another SQL Server 2005 instance?

    Your email has been sent

Share: How do I... Transfer logins from one SQL Server 2005 instance to another SQL Server 2005 instance?
Image of Steven S. Warren
By Steven S. Warren
Steven S. Warren is a popular author residing in Winter Haven, Florida with his wife and 2 children. As a columnist on such well-known IT web sites as Techrepublic.com, CNET, and ZDNET, Steven has published numerous articles. Additionally, Steven
  • Account Information

    Contact Steven S. Warren

    Your message has been sent

  • |
  • See all of Steven S.'s content

Daily Tech Insider

If you can only read one tech story a day, this is it.

TechRepublic TechRepublic
  • TechRepublic on Facebook
  • TechRepublic on X
  • TechRepublic on LinkedIn
  • TechRepublic on YouTube
  • TechRepublic on Pinterest
  • TechRepublic RSS
Services
  • About Us
  • Newsletters
  • RSS Feeds
  • Site Map
  • Site Help & Feedback
  • FAQ
  • Advertise
  • Do Not Sell My Information
  • Careers
Explore
  • Downloads
  • TechRepublic Forums
  • Meet the Team
  • TechRepublic Academy
  • TechRepublic Premium
  • Resource Library
  • Photos
  • Videos
  • Editorial Policy
  • Legal Terms
  • Privacy Policy
© 2025 TechnologyAdvice. All rights reserved.
CLOSE

Create a TechRepublic Account

Get the web's best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let's start with the basics.

Already registered? Sign In
Use Facebook
Use Linkedin

* - indicates required fields

CLOSE

Sign in to TechRepublic

Not a member? Create an account
Use Facebook
Use Linkedin

Lost your password? Request a new password

CLOSE

Reset Password

Please enter your email adress. You will receive an email message with instructions on how to reset your password.

Check your email for a password reset link. If you didn't receive an email don't forgot to check your spam folder, otherwise contact support.

Back to login
1 Finish Profile
2 Newsletter Preferences
CLOSE

Welcome. Tell us a little bit about you.

This will help us provide you with customized content.

No thanks, continue without
1 Finish Profile
2 Newsletter Preferences
CLOSE

Want to receive more TechRepublic news?

Newsletter Name
Subscribe
Daily Tech Insider
Daily Tech Insider AU
TechRepublic UK
TechRepublic News and Special Offers
TechRepublic News and Special Offers International
Executive Briefing
Innovation Insider
Project Management Insider
Microsoft Weekly
Cloud Insider
Data Insider
Developer Insider
TechRepublic Premium
Apple Weekly
Cybersecurity Insider
Google Weekly
Toggle All
No thanks, continue without

You're All Set

Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.

Back to Home Page
×