Move SQL User Names

Posted on 2011-10-21
Last Modified: 2012-05-12
I am backing up and restoring databases from a SQL 2005 server to a SQL 2008 server and I read that I can use the sp_help_revlogin to migrate but I am wondering a few things. What do I change if I only wnat a specific database and how do I run this to get the output?
Question by:bdseymou
    LVL 12

    Accepted Solution

    LVL 25

    Assisted Solution

    Does the login which is mapped to user is present in the target server? If yes, then you can simply restore the database and run the below script to manage orphan user. But if you don't have the login in the target server, you need to migrate the particular login first, followed by restoring the database and then running the below orphan script to map the user to the login.

    DECLARE @username varchar(25)
          DECLARE fixusers CURSOR

          SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
          and suser_sname(sid) is null and [name] in (select [name] from master.dbo.syslogins)
          ORDER BY name

          OPEN fixusers

          FETCH NEXT FROM fixusers
          INTO @username

          WHILE @@FETCH_STATUS = 0
          EXEC sp_change_users_login 'update_one', @username, @username
          FETCH NEXT FROM fixusers
          INTO @username

          CLOSE fixusers
          DEALLOCATE fixusers

    Author Comment

    I need to migrate all the accounts from the old server. NormanMaina:, I read that and in fact that is where I got the info for moing but I guess I want to make sure that I get what I need so if I am after a specific database then what do I need to modify in the script before running it?
    LVL 12

    Expert Comment

    The script captures all logins for a sql server instance and enables you to migrate them to another sql server instance.

    Logins are defined at the server level - that script will not check if a login is for a particular database -but will copy all logins on the intance
    LVL 1

    Expert Comment

    for migrating loginds i am using this free tool from idera

    hope it helps

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now