Moving SQL 6.5 logins

  • How would you move the user logins and passwords between two 6.5 database servers (They are not on the same network) Without restoring the master database of the source server onto the destination server.

    John Zacharkan

  • Should be able to bcp the contents of sysxlogins over - at least you can with 7.0 & 2000!


  • In 6.5 that would mean moving all the logins including sa - I'd prefer not to that. I would also have to drop all the logins on the dest server.

    Here's my solution using borrowed code: I exported the logins/passwords into a new table with somethng like this

    select @RPCText = @RemoteServer + "...sp_SQLExec 'select name, password from master..syslogins where suid > 10 and suid < 16382' "

    insert into TSyslogins

    exec (@RPCText)

    I backed up and restored that table on my dest server xfering the backup via a zip drive.

    With an anonymous password I added the logins via this script:

    declare name sysname(30)

    declare TSyslogins_cursor cursor

    for select name

    from TSyslogins

    open TSyslogins_cursor

    fetch next

    from TSyslogins_cursor

    into @name

    while (@@fetch_status = 0)


    exec sp_addlogin @name, 'password'

    fetch next

    from TSyslogins_cursor

    into @name


    close TSyslogins_cursor

    deallocate TSyslogins_cursor

    I then went back and corrected the passwords with this script:

    exec sp_configure "allow updates", 1

    reconfigure with override

    exec ("update master..syslogins

    set master..syslogins.password = MyRemoteSyslogins.password

    from TSyslogins

    where =")

    -- now disable updates to system tables

    exec sp_configure "allow updates", 0

    reconfigure with override

    John Zacharkan

  • My solution comea with my compliments to Brian Moran in an article he wrote

    You Can Transfer Encrypted Passwords Across Servers.

    For more info see

    John Zacharkan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply