Transferring datanase  users between legacy & new system

Posted on 2007-08-08
Last Modified: 2013-11-05
Hi, I need copy the existsing users & roles from one server to another due toackup a server uprade.  Curently all the databases etc have been transferred (I don't know if via backup / restore or other) but the users & roles have not been sent across.

The two servers old & new can see each others'nstances on network so I should be able to transact sql script from the legacy to the new yes?  

How best to achieve the transfer / copy of these tables and is there any other tables apart from users and roles I need consider ?

Question by:Kyliem
    LVL 11

    Expert Comment

    You can configure the servers to be linked servers and then query them by referencing the tables as:

    I'm not sure whether you are using SQL 2000, or 2005, but you would set up the linked servers in either Enterprise Manager or SQL Server Management Studio depending on your version.  You should be able to find a lot of tutorials on how to link the servers.

    Author Comment

    Hi, I creaqted my link but am a little confused now since I cannot query the names I want to insert into my sysxlogins table from the legacy system.

    That is:  I can query   Select * from sysxlogins which give sme my handful of existing names including sa and scheme and builtin/administrators  etc and I can quey the same from the linked server.

    Howrever, is I try and do a query to select the names from legacy that do not exist in new system I get zero rows returned?   I thought the 'name' field in sysxlogins table would be unique text and therefore be easily compared.

    The query I used is as below   (ERPLIve being my linked server name and G the alias to table)

    select * from ERPLive.master.dbo.sysxlogins G where name not in (select name from sysxlogins)

    If I were run the two selects 'as is' I get my independant results but if run as shown then zero rows returned but there are about 50 users in the linked table that do not exist in the new server table.

    Please can you advise/explain ?

    Author Comment

    p.s  I am using SQL 2000

    Author Comment

    HI, I have found the below Microsoft article on transferring the users and it states the selecting of records is discouraged so probably somethign to do with table makeup, especially since original user SID's will not get copied and hence logins orphaned.
    I am going to use the script inj the article when downtime allows but thankjs for your input.

    If no objection I intend to request a refund of points ?
    LVL 11

    Expert Comment

    no objection here, I'm glad you found a solution :)
    LVL 1

    Accepted Solution

    Closed, 200 points refunded.
    Community Support Moderator

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    729 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

    15 Experts available now in Live!

    Get 1:1 Help Now