tranfering logins between to sql 2000 servers
Kaido Kibin
i have a problem with transfering server logins between to sql 2000 servers.
i have 2 business critical database applications on two different sql
servers. i want to move those databases to new multi-instance
(active/active) cluster. there are about 30 server logins on my servers and
multiple databases. what is the best way to migrate those logins with
passwords and all permissions to new servers? i need to be sure that after
re-attaching database files on new server everything works fine and all
users and applications can connect to server without any major changes.
i have tried transfering logins with DTS packets (as described in sql server
books online) but it does not work correctly. all permissions are gone after
transfering. microsoft suggests that best way to do it is by backing up
master database and restoring it on new server. but i can't do this because
system database paths are totally different on cluster and after restoring
master database server does not start anymore.
is there any other way to do this? i need to be sure that my new server will
be 100% same as old one. and this full server migration have to be done very
quickly because down-time can be very short.
regards,
kaido
Richard Ding
how large your databases are, the downtime (time that the copying files
take) may vary.
Since you mentioned minimal downtime, an alternative is to use
sp_help_revlogin to transfer logins with intact original passwords. c.f. MS
technet article Q246133. Then restore databases and use
sp_change_users_login to get user accounts in sync with logins. User
permissions will stay the same.
We have adopted this approach to build a new SQL server in our environment.
All you need to do (which means the downtime) is to change the server name
to be the same as the old one and update system tables through dummy
installation.
Richard
"Kaido Kibin" <kaido...@abs.ee> wrote in message
news:udaKfHERCHA.2696@tkmsftngp13...