Moving SQL 6.5 logins

Question

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

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

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

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

Andy

zach_john SSCertifiable Points: 5922 More actions · Answer 2

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

zach_john SSCertifiable Points: 5922 More actions · Answer 3

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

You Can Transfer Encrypted Passwords Across Servers.

John Zacharkan