Switching domains for all app users

By: Chris Whisenhunt

Posted on: July 18, 2017

Our central IT is currently in the process of migrating all of our users from one domain to another. Below is a handy script that I’ve used to do just that.

set nocount on;

begin try
begin tran;

declare @CURRENTDATE datetime = getdate(),
@CHANGEAGENTID uniqueidentifier;

if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

declare @CURRENTDOMAIN nvarchar(10) = ‘scuad\’,
@NEWDOMAIN nvarchar(10) = ‘scu\’,
@CURRENTDOMAINLIKE nvarchar(11),
@NEWDOMAINLIKE nvarchar(11);

set @CURRENTDOMAINLIKE = @CURRENTDOMAIN + ‘%’;
set @NEWDOMAINLIKE = @NEWDOMAIN + ‘%’;

select
(select count(*) from APPUSER where USERNAME like @CURRENTDOMAINLIKE) CURRENTDOMAINCOUNTBEFOREMIGRATION,
(select count(*) from APPUSER where USERNAME like @NEWDOMAINLIKE) NEWDOMAINCOUNTBEFOREMIGRATION

declare @APPUSERS table(ID uniqueidentifier, USERSID varbinary(85));

insert into
@APPUSERS
select
ID,
suser_sid(replace(USERNAME, ‘scuad\’, ‘scu\’))
from
APPUSER
where
USERNAME like @CURRENTDOMAIN + ‘%’;

update
A
set
A.USERSID = U.USERSID,
A.CHANGEDBYID = @CHANGEAGENTID,
A.DATECHANGED = @CURRENTDATE
from
APPUSER A
inner join @APPUSERS U on A.ID = U.ID
where
U.USERSID is not null;

select
(select count(*) from APPUSER where USERNAME like @CURRENTDOMAINLIKE) CURRENTDOMAINCOUNTBEFOREMIGRATION,
(select count(*) from APPUSER where USERNAME like @NEWDOMAINLIKE) NEWDOMAINCOUNTBEFOREMIGRATION

rollback tran;
end try
begin catch
rollback tran;

exec USP_RAISE_ERROR;
end catch

Leave a Reply

Privacy Policy | Sitemap | © 2011 Blackbaud, Inc. All Rights Reserved

Digital Ocean