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!

    Andy

  • 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)

    begin

    exec sp_addlogin @name, 'password'

    fetch next

    from TSyslogins_cursor

    into @name

    end

    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 master..syslogins.name = TSyslogins.name")

    -- 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 http://www.winntmag.com/Articles/Index.cfm?ArticleID=102&pg=2


    John Zacharkan

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

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