Prev: How to create a new instance in SQL 2005 Server Standard?
Next: Login failed for user ''.
From: mir on 15 Oct 2009 06:41
Hi,

We have two AD (windows 2003) domains: CORPAD and UKCORP

In the SQL server, we have

these logins:

CORPAD/user1
CORPAD/user2
CORPAD/user3

We would like to change these to

UKCORP/user1
UKCORP/user2
UKCORP/user3

I would be really grateful if you could assist me to solve this.

Regards
Mir



David Hay wrote:

Re: how to change users login name in sql 2000 SP4 after domain change...
23-Nov-08

Make sure you run this in a test environment FIRST before running
against a production server! I cannot emphasize this enough. test,
test, test and more testing.

I used this and it worked pretty well. It will generate a script that
clones the old AD accounts, with all permissions intact. you can then
run the generated script in QA. It has been slightly modified. You
can search for the original author to see the original. Replace the
Old domain and New Domain values as appropriate. Remeber to set QA to
output text, and to turn column headings off.

You will need to check Job ownerships, search for Orphans, etc prior
to dropping the old AD accounts. When I did it, I also changed DTS
ownership, JobOwnership, I had many procedures that had embedded code
pointing to the old domain. You will also probably have to examine
any/all DTS connections for any references to the old domain. There
is a tool called DTSDOC that will generate a very nice HTML document
for you. That can then be searched so the packages can be updated.

good luck and GO SLOW!

David Hay


/*
*
* Quickly generate a script to add active directory users
* that match from an nt-4.0 domain controled setting
*
* by Jon Coulter
* 11/09/2004
*
* -- updated 3/10/2004 to allow for MSSQL 7 (changed 'name' to
'loginname' from syslogins tabe)
*/

/*
select o.loginname, o.sid
from master.dbo.syslogins o,
master.dbo.syslanguages l
where (o.language like l.alias or o.language like l.name)
*/


-- select o.loginname, o.language, o.dbname, N'sa' = convert(int,
suser_sid(o.name)), o.sid, o.denylogin, o.isntname, o.isntgroup,
o.password, l.alias, o.hasaccess, o.sysadmin from master.dbo.syslogins
o, master.dbo.syslanguages l where (o.language like l.alias or
o.language like l.name) union select o.loginname, l.name, o.dbname,
N'sa' = convert(int, suser_sid(o.name)), o.sid, o.denylogin,
o.isntname, o.isntgroup, o.password, l.alias, o.hasaccess, o.sysadmin
from master.dbo.syslogins o, master.dbo.syslanguages l where
o.language is NULL and l.langid = @@default_langid order by
o.isntgroup, o.loginname
-- select loginname, sid from master.dbo.syslogins where sid is not
null
-- select * from master..syslogins

-- select cast
(0x01050000000000051500000079415968DF509D2D454AEF1401020000 as
varbinary)

-- note, remove column headers (Tools -> Options -> Results -> uncheck
column headers)
-- to get nice output
use master
go

IF OBJECT_ID ('sp_hexadecimal') IS NULL
exec('
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ''0x''
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ''0123456789ABCDEF''
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue'
)
GO

begin
set nocount on

declare @nt_domain varchar(100),
@ad_domain varchar(100)

set @nt_domain = 'Old Domain'
set @ad_domain = 'New Domain'

-- no need to edit below this line
IF OBJECT_ID ('sp_hexadecimal') IS NULL
raiserror('You must first load the stored procedure sp_hexadecimal
(from the sp_help_revlogin.sql script)', 15, 1)
else
begin
print '-- Converting from [' + @nt_domain + '] to [' + @ad_domain +
']'
print '-- Ouput Generated: ' + cast(getdate() as varchar)
print ''
print '-- Allow updates to system tables:'
print 'exec sp_configure updates, 1'
print 'reconfigure with override'
print 'go'
print ''


declare @like_clause varchar(102)
set @like_clause = isnull(@nt_domain, '') + '\%';

-- temporary srvrole access table
create table #srvrole
(
rolename varchar(150),
member varchar(150),
sid varbinary(100)
);

-- insert data
insert into #srvrole
exec sp_helpsrvrolemember

-- delete invalid users
delete from #srvrole
where member not like @like_clause

-- change their name to the new one
update #srvrole
set member = substring(member, charindex('\', member) + 1, len
(member));

declare @name varchar(100),
@sid varbinary(100),
@shortname varchar(100),
@dbname varchar(100)
declare c_users cursor for
select loginname, sid,
substring(loginname, charindex('\', loginname) + 1, len
(loginname)),
dbname
from master..syslogins
where UPPER(loginname) like @like_clause

open c_users
fetch from c_users into @name, @sid, @shortname, @dbname

while(@@fetch_status = 0)
begin
declare @ad_name varchar(150)
declare @hexsid as varchar(256)
set @ad_name = @ad_domain + '\' + @shortname

set @hexsid = '';
exec sp_hexadecimal @sid, @hexsid OUTPUT -- get the hex value of
this

print '--'
print '-- Create user [' + @ad_name + '] based on [' + @name + ']'
print '--'

print '-- Grant login:'
print 'exec sp_grantlogin ''' + @ad_name + ''''

if(@dbname is not null)
begin
print '-- Set Default Database:'
print 'exec sp_defaultdb ''' + @ad_name + ''', ''' + @dbname +
''''
end

-- server-level grants for that user
print ''
print '-- Grant any server-roles:'
select 'exec sp_addsrvrolemember ''' + @ad_name + ''', ''' +
rolename + ''''
from #srvrole
where lower(member) = lower(@shortname)

-- now print and update statement to update that user in the user
database
-- select sid, name from sysusers
print '-- Update user databases with newly maped users:'
declare @updatestr varchar(1000)
set @updatestr = '
if(''?'' not in (''master'', ''model'', ''tempdb'', ''msdb''))
print ''update [?]..[sysusers] set sid = isnull((select top 1
sid from master..syslogins where name = ''''' + @ad_name + '''''),
sid) where sid = cast(' + @hexsid + ' as varbinary)''
'
exec sp_MSforeachdb @updatestr

print '-- Completed creating/mapping user: [' + @ad_name + ']'
print ''

fetch from c_users into @name, @sid, @shortname, @dbname
end

-- added 12/19/2007 David Hay
declare @updatestr2 varchar(1000)
set @updatestr2 = '
if(''?'' not in (''master'', ''model'', ''tempdb'', ''msdb''))
print ''update [?]..[sysusers] set name = SUSER_SNAME(sid) where
name like ''''' + @like_clause + '''''
'''
exec sp_MSforeachdb @updatestr2


print 'use master'
print 'go'
print 'update sysdatabases set sid = suser_sid(replace(suser_sname
(sid),''' + @nt_domain + '\'',''' + @ad_domain + '\'')) where
suser_sname(sid) like ''' + @nt_domain + '\%'''


print '-- for good measure:'
print 'go'
print ''
print '-- Dis-allow updates to system tables:'
print 'exec sp_configure updates, 0'
print 'reconfigure with override'
print 'go'

close c_users
deallocate c_users

drop table #srvrole
end
end

Previous Posts In This Thread:

On 21 November 2008 09:07
Imran wrote:

how to change users login name in sql 2000 SP4 after domain change...
Hi,

I migrated windows domain & users and it happened that the users now belong
to a new domain domain2,
The users in sql server 2000 SP4 are still defined as part of domain1,
now I needs to some how change all sql server 2000 users from the domain1 to
the domain2 (just the name of the accounts, users or logins).

Any idea please. if it s also to go through users and change their
credentials in sql 2000 to reflect the new domain name.

Thank you.

On 21 November 2008 14:28
Michae wrote:

RE: how to change users login name in sql 2000 SP4 after domain change
The user name is not really your problem. It is not used during the
authentication process in SQL Server. When you add a domain user to SQL
Server it stores the SID and uses that. If you created new users in the new
domain then they will have new SID's and you will have to re-add them to SQL
Server. Of course how you moved the users to the new domain may make it
possible to fix. There are ways to move users from one domain to another and
keep the SIDs. If you did then we may have something to work with...

Michael

"Imran" wrote:

On 23 November 2008 04:48
David Hay wrote:

Re: how to change users login name in sql 2000 SP4 after domain change...
Make sure you run this in a test environment FIRST before running
against a production server! I cannot emphasize this enough. test,
test, test and more testing.

I used this and it worked pretty well. It will generate a script that
clones the old AD accounts, with all permissions intact. you can then
run the generated script in QA. It has been slightly modified. You
can search for the original author to see the original. Replace the
Old domain and New Domain values as appropriate. Remeber to set QA to
output text, and to turn column headings off.

You will need to check Job ownerships, search for Orphans, etc prior
to dropping the old AD accounts. When I did it, I also changed DTS
ownership, JobOwnership, I had many procedures that had embedded code
pointing to the old domain. You will also probably have to examine
any/all DTS connections for any references to the old domain. There
is a tool called DTSDOC that will generate a very nice HTML document
for you. That can then be searched so the packages can be updated.

good luck and GO SLOW!

David Hay


/*
*
* Quickly generate a script to add active directory users
* that match from an nt-4.0 domain controled setting
*
* by Jon Coulter
* 11/09/2004
*
* -- updated 3/10/2004 to allow for MSSQL 7 (changed 'name' to
'loginname' from syslogins tabe)
*/

/*
select o.loginname, o.sid
from master.dbo.syslogins o,
master.dbo.syslanguages l
where (o.language like l.alias or o.language like l.name)
*/


-- select o.loginname, o.language, o.dbname, N'sa' = convert(int,
suser_sid(o.name)), o.sid, o.denylogin, o.isntname, o.isntgroup,
o.password, l.alias, o.hasaccess, o.sysadmin from master.dbo.syslogins
o, master.dbo.syslanguages l where (o.language like l.alias or
o.language like l.name) union select o.loginname, l.name, o.dbname,
N'sa' = convert(int, suser_sid(o.name)), o.sid, o.denylogin,
o.isntname, o.isntgroup, o.password, l.alias, o.hasaccess, o.sysadmin
from master.dbo.syslogins o, master.dbo.syslanguages l where
o.language is NULL and l.langid = @@default_langid order by
o.isntgroup, o.loginname
-- select loginname, sid from master.dbo.syslogins where sid is not
null
-- select * from master..syslogins

-- select cast
(0x01050000000000051500000079415968DF509D2D454AEF1401020000 as
varbinary)

-- note, remove column headers (Tools -> Options -> Results -> uncheck
column headers)
-- to get nice output
use master
go

IF OBJECT_ID ('sp_hexadecimal') IS NULL
exec('
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ''0x''
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ''0123456789ABCDEF''
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue'
)
GO

begin
set nocount on

declare @nt_domain varchar(100),
@ad_domain varchar(100)

set @nt_domain = 'Old Domain'
set @ad_domain = 'New Domain'

-- no need to edit below this line
IF OBJECT_ID ('sp_hexadecimal') IS NULL
raiserror('You must first load the stored procedure sp_hexadecimal
(from the sp_help_revlogin.sql script)', 15, 1)
else
begin
print '-- Converting from [' + @nt_domain + '] to [' + @ad_domain +
']'
print '-- Ouput Generated: ' + cast(getdate() as varchar)
print ''
print '-- Allow updates to system tables:'
print 'exec sp_configure updates, 1'
print 'reconfigure with override'
print 'go'
print ''


declare @like_clause varchar(102)
set @like_clause = isnull(@nt_domain, '') + '\%';

-- temporary srvrole access table
create table #srvrole
(
rolename varchar(150),
member varchar(150),
sid varbinary(100)
);

-- insert data
insert into #srvrole
exec sp_helpsrvrolemember

-- delete invalid users
delete from #srvrole
where member not like @like_clause

-- change their name to the new one
update #srvrole
set member = substring(member, charindex('\', member) + 1, len
(member));

declare @name varchar(100),
@sid varbinary(100),
@shortname varchar(100),
@dbname varchar(100)
declare c_users cursor for
select loginname, sid,
substring(loginname, charindex('\', loginname) + 1, len
(loginname)),
dbname
from master..syslogins
where UPPER(loginname) like @like_clause

open c_users
fetch from c_users into @name, @sid, @shortname, @dbname

while(@@fetch_status = 0)
begin
declare @ad_name varchar(150)
declare @hexsid as varchar(256)
set @ad_name = @ad_domain + '\' + @shortname

set @hexsid = '';
exec sp_hexadecimal @sid, @hexsid OUTPUT -- get the hex value of
this

print '--'
print '-- Create user [' + @ad_name + '] based on [' + @name + ']'
print '--'

print '-- Grant login:'
print 'exec sp_grantlogin ''' + @ad_name + ''''

if(@dbname is not null)
begin
print '-- Set Default Database:'
print 'exec sp_defaultdb ''' + @ad_name + ''', ''' + @dbname +
''''
end

-- server-level grants for that user
print ''
print '-- Grant any server-roles:'
select 'exec sp_addsrvrolemember ''' + @ad_name + ''', ''' +
rolename + ''''
from #srvrole
where lower(member) = lower(@shortname)

-- now print and update statement to update that user in the user
database
-- select sid, name from sysusers
print '-- Update user databases with newly maped users:'
declare @updatestr varchar(1000)
set @updatestr = '
if(''?'' not in (''master'', ''model'', ''tempdb'', ''msdb''))
print ''update [?]..[sysusers] set sid = isnull((select top 1
sid from master..syslogins where name = ''''' + @ad_name + '''''),
sid) where sid = cast(' + @hexsid + ' as varbinary)''
'
exec sp_MSforeachdb @updatestr

print '-- Completed creating/mapping user: [' + @ad_name + ']'
print ''

fetch from c_users into @name, @sid, @shortname, @dbname
end

-- added 12/19/2007 David Hay
declare @updatestr2 varchar(1000)
set @updatestr2 = '
if(''?'' not in (''master'', ''model'', ''tempdb'', ''msdb''))
print ''update [?]..[sysusers] set name = SUSER_SNAME(sid) where
name like ''''' + @like_clause + '''''
'''
exec sp_MSforeachdb @updatestr2


print 'use master'
print 'go'
print 'update sysdatabases set sid = suser_sid(replace(suser_sname
(sid),''' + @nt_domain + '\'',''' + @ad_domain + '\'')) where
suser_sname(sid) like ''' + @nt_domain + '\%'''


print '-- for good measure:'
print 'go'
print ''
print '-- Dis-allow updates to system tables:'
print 'exec sp_configure updates, 0'
print 'reconfigure with override'
print 'go'

close c_users
deallocate c_users

drop table #srvrole
end
end

EggHeadCafe - Software Developer Portal of Choice
Getting PC information
http://www.eggheadcafe.com/tutorials/aspnet/ecc09268-5597-4392-8c65-58b133610606/getting-pc-information.aspx
 | 
Pages: 1
Prev: How to create a new instance in SQL 2005 Server Standard?
Next: Login failed for user ''.