Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    9

    Unanswered: merging of seperate syslogins

    Hello,

    We have a requirement to place the databases & users from 2 seperate Sybase 11.9.2 servers and rebuild them onto a single entity also on 11.9.2.

    I have used the following procedure to bcp out the syslogins from 1 server onto the new machine but I need to know how i can then import the 2nd set of syslogins bearing in mind that a large percentage of them will be the same user.

    Steps for migrating logins from OLDSERVERNAME to NEWSERVERNAME

    1. bcp out syslogins from OLDSERVERNAME using
    bcp master..syslogins out OLDSERVERNAME_logins.txt –Usa –Pxxxx -SOLDSERVERNAME–c –t,

    2. Manually delete the first two rows from OLDSERVERNAME_logins.txt file.
    [ Make sure it is ‘sa’ and ‘mon_user’ with suid 1 and 3 ]

    3. Delete syslogins entry by using
    sp_configure ‘allow updates’,1
    go
    Delete from master..syslogins where suid > 3
    Go

    4. bcp master..syslogins in OLDSERVERNAME_logins.txt –Usa –Pxxxx -SNEWSERVERNAME -c –t,

    5. Run the below script on each databases which are refreshed from OLDSERVER.

    EXEC sp_configure "allow updates", 1
    go

    /* delete names not found in master syslogins */
    DELETE FROM sysusers
    WHERE suid > 1
    and uid < =16383
    and uid > 1
    and name not in
    (SELECT name FROM master..syslogins)
    go

    /* update sysusers and resync all uids from the existing suids */
    IF exists(SELECT 1
    FROM sysobjects
    WHERE name = "sysusers_holding"
    and type = "U")
    DROP TABLE sysusers_holding
    go

    CREATE TABLE sysusers_holding (
    id NUMERIC(6,0) IDENTITY PRIMARY KEY,
    suid INT null,
    uid INT null,
    gid INT null,
    name CHAR(30) null,
    environ VARCHAR(255) null)
    go

    /* populate the holding table */
    INSERT INTO sysusers_holding (suid, gid, name, environ)
    SELECT suid, gid, name, environ
    FROM sysusers
    WHERE suid > 1
    and uid < = 16383
    and uid > 1
    go

    /* update the uids. */
    DECLARE syncuser CURSOR FOR
    SELECT name, uid
    FROM sysusers_holding
    FOR UPDATE OF uid, suid
    go

    DECLARE @name CHAR(30), @uid INT, @suid INT
    SELECT @suid = 9999
    OPEN syncuser
    FETCH syncuser INTO @name, @uid
    WHILE (@@sqlstatus != 2)
    BEGIN
    IF exists(SELECT 1
    FROM master..syslogins
    WHERE name = @name)
    /* got the name - update the uid */
    BEGIN
    SELECT @suid = suid
    FROM master..syslogins
    WHERE name = @name
    UPDATE sysusers_holding SET uid = @suid, suid = @suid WHERE CURRENT OF syncuser

    IF @@ERROR != 0
    BEGIN
    ROLLBACK TRANSACTION
    BREAK
    END

    SELECT @suid = 9999
    END
    FETCH syncuser INTO @name, @uid
    END
    CLOSE syncuser
    go

    DEALLOCATE CURSOR syncuser
    go

    /* now move records over */
    DELETE FROM sysusers
    WHERE suid > 1
    and uid < =16383
    and uid > 1
    go

    INSERT INTO sysusers
    SELECT suid, uid, gid, name, environ
    FROM sysusers_holding
    go

    /* sp_helpuser will show any mis-matches) */
    EXEC sp_helpuser
    go

    EXEC sp_configure "allow updates", 0
    go


    any help greatly appreciated both for the current process I'm using and getting the 2nd set of users imported

    Regards

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    A few questions:
    - Are there logins that exist in both old servers?
    - Will userdb's from both old servers be migrated to the new server?

    And the most important one: Is it neccesary to copy existing passwords from both servers to the new server?
    If not, you can just generate the create-loginscripts and create-userscripts on the old servers and run them on the new server. That way the logins and users wille created properly, with minimal risk of something happening during the manipulation of systemtables.
    You can set the paswords to just something, and the users can change it to something else.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    May 2011
    Posts
    9
    Quote Originally Posted by Martijnvs View Post
    A few questions:
    - Are there logins that exist in both old servers?
    - Will userdb's from both old servers be migrated to the new server?

    And the most important one: Is it neccesary to copy existing passwords from both servers to the new server?
    If not, you can just generate the create-loginscripts and create-userscripts on the old servers and run them on the new server. That way the logins and users wille created properly, with minimal risk of something happening during the manipulation of systemtables.
    You can set the paswords to just something, and the users can change it to something else.
    Hi,

    - yes, there are logins that exist on both old servers
    - yes, all databases from both servers need to be migrated to the new one
    - ideally yes, if there is a process to enable the users to continue using their current passwards then the business sponser would choose that. however if there is an increased risk of the process not working then we could make an arguement for users to have to change their passwords after the migration

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    First of all, it's an interesting case you have . Bit of a challenge, but fun.

    Although your posted procedure looks like it will work, there's a lot of manipulating systemtables, and that's something you want to avoid as much as possible.
    I assume you copy the databases with a dump/load-command?

    How about the following process:
    1: bcp-out the logins from the old servers (use flag -F<number> to specify the row at which to start, to skip sa & monuser)
    2: recreate all logins needed from oldserver1. do not recreate the users, as they're already present in the loaded databases.
    3: userdb..sysusers is probeblay mismatched. Using 'allow update', update sysusers.suid with the corresponding suid from master..syslogins, joinging both table on column 'name' (assuming loginname and username are identical)
    At this point you should have the proper logins with matching users in the databases from oldserver1. All privileges granted to the users should be kept in place. Check that if needed/wanted.

    4: bcp in the login-export from oldserver1 in a temporary table in newserver1 (either in masterdb or tempdb, doesnt matter too much).
    5: using 'allow update', update master..sysusers, column password, with the proper value from the temptable. join the tables on name, not on suid. The suid is not necessarily the same, since you recreated the logins.
    The logins should have the password from oldserver1 by now.

    Repeat the steps above for oldserver2 and it's databases. You probably will get errors creating logins that are already present from oldserver1. That is no problem, they wont be overwritten.
    Be advised that when you get to step 5, you will overwrite the passwords of the logins that are present in both oldserver1 and oldserver2. The logins will end up with the password of oldserver2. Not really a problem, just something for the user to know.

    Disclaimer: I haven't tested this myself, so be a little bit careful .
    I'm not crazy, I'm an aeroplane!

Posting Permissions

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