July 5, 2007 at 3:08 am
Hi,
We are to undergo a Domain Change and therefore current Domain users will need to be recreated under the new Domain, with the same permissions.
My thoughts were to try and script out all details and prepare a script to run which would recreate the profiles under the new Domain.
Any suggestions on how to achieve this are welcomed.
Thanks
Colin
July 5, 2007 at 5:10 am
You can script the users and the object level permissions with the generate script option that is availbale default in SQL 2000. This will script all the necessary permissions for you.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 5, 2007 at 6:46 am
Sugesh,
Thanks for the pointer. In looking at this area I think that I will need to create 3 scripts for each Database on the SQL Instance and run them in the following order, after having changed the reference from the old Domain to the new Domain.
Script SQL Server Logins
Script Database Users
Script Object Permissions.
As this is a considerable amount of work I would like to check my understanding first. Thanks.
Colin
July 5, 2007 at 7:09 am
You can script all in the same file and run against the target server. Why do you want script out each separately.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 5, 2007 at 7:18 am
Just trying to understand the process and ensure that things get created in the right order. A user cannot be allocated to a database if they have not already been created as a server login.
I can run the 3 options together, but will still need to run a script against each database?
Sorry to be slow on the uptake, just want to make sure that I really understand what I will be doing.
Cheers
colin
July 5, 2007 at 8:05 am
If that's the case then script out the logins first, then the database user and finally the object level permissions. Then run against the target server. It good to get things clarified until you have a clear picture i don't mind answering your questions so no worries you can fire tham till you get thing clarified.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 5, 2007 at 8:14 am
Thanks. I think it best to run them in squequence, suits my tidy mind.;-)
Am I right in saying that I need to do the same thing for each database, including system ones?
Cheers
colin
July 5, 2007 at 11:27 pm
Yes script for each db and run against it in server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 6, 2007 at 7:54 am
We did this about a year ago, but we used some tool to do it. Make sure you change the owners too. I'm not sure what the outcome would be if they weren't changed, but we had to check and change:
database owners, job owners, maintenance plan owners, object owners, and dts package owners
July 6, 2007 at 7:58 am
A while back, I wrote a script to generate the Alter/update statements by way of chaning the Old_domain to New_domain. Below is the script;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--SQL 2000 migration script for DBA use, to change the old_domain_name prefix to new_domain_name.
-- DBA: You still need to make sure that you are re-attaching the user or group to fixed role with the new name new_domain_name\..........
-- Written by Wali.
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
SET CURSOR_CLOSE_ON_COMMIT OFF
SET ANSI_NULLS ON
SET ANSI_WARNINGS OFF -- to eliminated the null values....
SET CONCAT_NULL_YIELDS_NULL ON
declare @icount as int ,
@next as sysname,
@sql1 as varchar(100),
@sql2 as varchar(100)
SET @sql1 = 'Exec SP_CONFIGURE '
SET @sql1 = @sql1 + "'allow updates'"
SET @sql1 = @sql1 + ',1' + char(10) + 'GO'
SET @sql2 = 'RECONFIGURE WITH OVERRIDE' + char(13) + char(10) + 'GO'
select @sql1
select @sql2
SET @icount = 0
SELECT @icount = (select count(*) from sysusers where charindex('old_domain_name\',name) > 0)
If @icount = 0 -- check for NT names without domain prefix at cont2 label....
Begin
Print '-->>>> NOT FOUND - NT Account or NT Group with [old_domain_name] prefix found in database [' +db_name() + '] Server: ' + @@servername
Print ''
goto cont
end
--continue with update
set nocount on
Print '-->>>> FOUND - NT Account and/or NT Group with [old_domain_name] prefix [' +db_name() + '] Server: ' + @@servername
Print ''
select @next = ''
while @next is NOT NULL
begin
select @next = min(name)
from sysusers
where charindex('old_domain_name\',name) > 0 and uid <> 1 and name > @next
if @next is not null
begin
declare @sqlstmt as varchar(1000)
set @sqlstmt = 'update sysusers set name = STUFF(''old_domain_name\'', 1, 4, ''new_domain_name'')+substring( +'''+@next+''' ,6,50)' +
' where name = ' + ''''+@next+''''
select @sqlstmt
end
end
IF @@error = 0
Begin
Print '-- >>>>>> Complete, generating required domain name changes for NT Account or NT Group from [old_domain_name] to [new_domain_name] <<<<<< '
end
else
Print '-- >>>>>> Failed, generating require domain name changes for NT and/or User group from [old_domain_name] to [new_domain_name] <<<<<< '
cont: --continue adding domain prefix to NT User or Group where missing
SET @icount = 0
SELECT @icount = (select count(*) from sysusers where isntname = 1 and charindex('new_domain_name\',name) = 0)
If @icount = 0 -- exit out....
Begin
Print '-->>>>> NOT FOUND - NT Account or NT Group with domain prefix missing in database [' +db_name() + '] Server: ' + @@servername
goto exitdomain
end
Print '-->>>>> FOUND - NT Account and/or NT Group with Domain prefix missing [' +db_name() + '] Server: ' + @@servername
select @next = ''
while @next is NOT NULL
begin
select @next = min(name)
from sysusers
where
charindex('\',name) = 0
and isntname = 1 and uid <> 1 and name > @next
if @next is not null
begin
set @sqlstmt = 'update sysusers set name = STUFF(''12345'', 1, 5, ''new_domain_name\'')+substring( +'''+@next+''' ,1,50)' +
' where name = ' + ''''+@next+''''
select @sqlstmt
-- exec (@sqlstmt)
end
end
IF @@error = 0
Begin
Print '-- >>>>>> Complete, adding required [new_domain_name]domain name to NT Account or NT Group <<<<<< '
end
else
Print '-- >>>>>> Failed, adding missing domain name [new_domain_name] for NT User and/or Group from <<<<<< '
exitdomain:
-- WALI... code to handle mis-match NT SID fix if required... After validating Domain name fix script run
select @next = ''
Declare @ntstr as varchar(1000)
Declare @nextname as varchar(1000)
while @next is NOT NULL
begin
select @next = min(u.name)
from sysusers as u inner join master.dbo.syslogins as l
on u.sid <> l.sid
Where u.isntname = 1 and
uid <> 1 and
u.sid <> l.sid and u.name > @next
-- logic to handle domain prefix for NT account/group from old_domain_name to new_domain_name....
set @nextname = CASE when charindex('\',@next) > 0 then
'new_domain_name\' + substring(@next,6,50)
ELSE 'new_domain_name\' + @next
END
Begin
print ''
Print '-- >>>>>> fixing SID for NT user/group ==> ' + Quotename(@next,"'")
set @ntstr = 'update u set u.sid = l.sid, u.name = l.name from sysusers as u inner join master.dbo.syslogins as l'+ char(10) +
' on l.name = u.name Where u.isntname = 1 and u.name = '+QUOTENAME(+@nextname,"'") + ' and u.sid <> l.sid '
print @ntstr
end
end
-- Reset the configuration override.
SET @sql1 = 'Exec SP_CONFIGURE '
SET @sql1 = @sql1 + "'allow updates'"
SET @sql1 = @sql1 + ',0' + char(10) + 'GO'
SET @sql2 = 'RECONFIGURE WITH OVERRIDE' + char(13) + char(10) + 'GO'
select @sql1
select @sql2
SET NOCOUNT OFF
SET QUOTED_IDENTIFIER ON
SET CURSOR_CLOSE_ON_COMMIT ON
SET ANSI_NULLS OFF
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL OFF
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
htht,
Wali
July 10, 2007 at 6:39 am
I have a script/stored procedure pair. If you're interested message me and I can send it along.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply