February 20, 2008 at 5:58 am
I am running a virtual test lab in preparation for a domain separation project and am now testing moving a SQL server from one domain to another. there are 2 windows 2003 domains, and the SQL server is on a non-DC windows 2003 server.
On the SQL server I have created a database called db1 and created login users test.user1 and test.user2 which are domain users, not SQL users, and assigned them rights to the db1 database. I have migrated the test.user1 account to the new domain, and then used the active directory migration tool to migrate the server. This went fine, the service account is local system so that's fine and working, but the test.user1 user is still pointing to the old domain, even though test.user1's account and mailbox have been migrated successfully to the new domain. I can see that user security/logins in the enterprise manager that test.user1 is still pointing to the old domain. is there a way i can make this windows user 'update' in sql server or will i have to create the logins again in sql manually?
thanks in advance
andoni
February 20, 2008 at 6:50 am
This will work, but i have not tried it in a production environment.... so test then test test ! once you do this...SIDS may get out of sync...so use script 2 to re link them...
Update sysxlogins set name = ' DOMAINB' + substring(name, 9, LEN(name)-8
)
where name like 'DOMAINA%'
Will replace the SQL2000 domain name correctly in sysxlogins:
DOMAINA \JonesP becomes DOMAINB\JonesP
Script 2
set nocount on
go
if exists(select * from tempdb..sysobjects where id =
object_id('tempdb..#t_users'))
drop table #t_users
CREATE TABLE #t_users ( [name] sysname)
INSERT #t_users ( [name] )
SELECT [name] from sysusers where status = 2 and name <> 'dbo' order by name
declare @lc_name sysname
SET @lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @lc_name IS NOT NULL
BEGIN
IF exists(select * FROM master..syslogins WHERE [name] = @lc_name)
EXEC sp_change_users_login 'AUTO_FIX', @lc_name
else
PRINT '*** not fixing ' + @lc_name
SET @lc_name = (SELECT MIN([name]) FROM #t_users WHERE [name] >
@lc_name )
END
go
Oraculum
February 21, 2008 at 4:29 am
excellent! thank you so much! 🙂
andoni
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply