Transferring datanase users between legacy & new system

Hi, I need copy the existsing users & roles from one server to another due toackup a server uprade.  Curently all the databases etc have been transferred (I don't know if via backup / restore or other) but the users & roles have not been sent across.

The two servers old & new can see each others'nstances on network so I should be able to transact sql script from the legacy to the new yes?  

How best to achieve the transfer / copy of these tables and is there any other tables apart from users and roles I need consider ?

Who is Participating?
Vee_ModConnect With a Mentor Commented:
Closed, 200 points refunded.
Community Support Moderator
You can configure the servers to be linked servers and then query them by referencing the tables as:

I'm not sure whether you are using SQL 2000, or 2005, but you would set up the linked servers in either Enterprise Manager or SQL Server Management Studio depending on your version.  You should be able to find a lot of tutorials on how to link the servers.
KyliemAuthor Commented:
Hi, I creaqted my link but am a little confused now since I cannot query the names I want to insert into my sysxlogins table from the legacy system.

That is:  I can query   Select * from sysxlogins which give sme my handful of existing names including sa and scheme and builtin/administrators  etc and I can quey the same from the linked server.

Howrever, is I try and do a query to select the names from legacy that do not exist in new system I get zero rows returned?   I thought the 'name' field in sysxlogins table would be unique text and therefore be easily compared.

The query I used is as below   (ERPLIve being my linked server name and G the alias to table)

select * from ERPLive.master.dbo.sysxlogins G where name not in (select name from sysxlogins)

If I were run the two selects 'as is' I get my independant results but if run as shown then zero rows returned but there are about 50 users in the linked table that do not exist in the new server table.

Please can you advise/explain ?
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

KyliemAuthor Commented:
p.s  I am using SQL 2000
KyliemAuthor Commented:
HI, I have found the below Microsoft article on transferring the users and it states the selecting of records is discouraged so probably somethign to do with table makeup, especially since original user SID's will not get copied and hence logins orphaned.
I am going to use the script inj the article when downtime allows but thankjs for your input.

If no objection I intend to request a refund of points ?
no objection here, I'm glad you found a solution :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.