Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

Move SQL User Names

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?
2 Solutions
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

      EXEC sp_change_users_login 'update_one', @username, @username
      FETCH NEXT FROM fixusers
      INTO @username

      CLOSE fixusers
      DEALLOCATE fixusers
bdseymouAuthor Commented:
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?
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
Elias SaadéSenior DBACommented:
for migrating loginds i am using this free tool from idera


hope it helps

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now