Kyliem
asked on
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
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
ASKER
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.sysxlog
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 ?
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.sysxlog
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 ?
ASKER
p.s I am using SQL 2000
ASKER
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/
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/
no objection here, I'm glad you found a solution :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
server.database.owner.tabl
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.