Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    California
    Posts
    18

    Unhappy Unanswered: merging two mssql db

    I have a problem that I need help on.

    Right now, I have two MSSQL server and I am trying to merge them into one.

    The problem is, I do not know the login Id and password for some of the users.

    I know the login information are stored under syslogins at the master db.

    How do I get the information out and append to the syslogins table of the second mssql db?

    Any help would be appreciated.

    ---alucarrd

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Try to use linked server and query like this:

    insert sysxlogins
    (srvid,sid,xstatus,xdate1,xdate2,name,password,dbi d,language)
    select srvid,sid,xstatus,xdate1,xdate2,name,password,dbid ,language
    from remote.master.dbo.sysxlogins
    where name='name'-- if not all of them

  3. #3
    Join Date
    Aug 2003
    Location
    California
    Posts
    18

    Smile

    Thank you snail,

    I can smile now.

    However, I tried with this statement, and now I am getting an error:

    Cannot insert duplicate key row in object 'sysxlogins' with unique index 'sysxlogins'.
    The statement has been terminated.

    I got the linked server set up correctly and I can see the information of the remote server by checking the select statement on the sysxlogins table in the remote server.

    However, I ran the script you showed me, and that's the error I got.

    This is the script that I ran:

    insert sysxlogins(srvid,sid,xstatus,xdate1,xdate2,name,
    password,dbid,language)select srvid,sid,xstatus,xdate1,xdate2,
    name,password,dbid,language
    from remote1.master.dbo.sysxlogins where name<>'sa'

    Thank you for helping me out.

  4. #4
    Join Date
    Aug 2003
    Location
    California
    Posts
    18

    Talking found out why...

    I think the reason I got the error is because the user exist already on the destination machine.

    Thank you.

    I will just go from here.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •