Announcement

As of April 17, 2018, this site is now in read-only mode. To start new Blackbaud Developer’s discussions, you will find Developer Discussions in the Blackbaud CRM Community.

To post or respond to Blackbaud Community discussions, you will have to login using a www.blackbaud.com login. Learn how to login here. If you have questions or need assistance, please email community@blackbaud.com.

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