• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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 ?

Thanks
K
0
Kyliem
Asked:
Kyliem
  • 3
  • 2
1 Solution
 
SwindleCommented:
You can configure the servers to be linked servers and then query them by referencing the tables as:
server.database.owner.table

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.
0
 
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 ?
0
 
KyliemAuthor Commented:
p.s  I am using SQL 2000
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 ?
ARTICLE
http://support.microsoft.com/kb/246133/
0
 
SwindleCommented:
no objection here, I'm glad you found a solution :)
0
 
Vee_ModCommented:
Closed, 200 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now