SQL Server tips: How to migrate database-users in another domain without touching permissions

By a customer, I must migrate a database from a domain to another.
The goal is to keep the database-user permissions, take the new SID from the windows login of the new domain and change the database-user name.
In my blog, I will do it with two dbi’s domains:
– dbiservicech: The old domain
– dbiservicecorp: The new domain

I have two Windows groups dbiservicech\dbi-owner and dbiservicech\dbi-reader in my database test-dbi.
I create two new AD groups in the new domain dbiservicecorp\dbi-owner and dbiservicecorp\dbi-reader.
I add these 2 groups in the instance:

USE [master]
GO
CREATE LOGIN [dbiservicecorp\dbi-owner] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [dbiservicecorp\dbi-reader] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

The goal is to change the name of these groups inside the database:
– From dbiservicech\dbi-owner to dbiservicecorp\dbi-owner
– From dbiservicech\dbi-reader to dbiservicecorp\dbi-reader

If I want to see the SID and name before the change, i use these select:

select sid,name from sys.syslogins
select sid,name from [test-dbi].sys.sysusers

The result told me that the SID and name are not aligned between the login & database-user:

sid 0x0105000000000005150000000F43744E3AB07D23ECE90E12B4CA0200 for the login dbiservicecorp\dbi-owner
sid 0x0105000000000005150000000F43744E3AB07D23ECE90E12B5CA0200 for the login dbiservicecorp\dbi-reader

sid 0x010500000000000515000000F3092A0E6DFA8A62697C50F4347D0000 for the database-user dbiservicech\dbi-owner
sid 0x010500000000000515000000F3092A0E6DFA8A62697C50F4357D0000 for the database-user dbiservicech\dbi-reader

To adapt the SID and name, I use the ALTER USER command:

USE [test-dbi];
GO
ALTER USER [dbiservicech\dbi-owner] WITH NAME = [dbiservicecorp\dbi-owner], LOGIN=[dbiservicecorp\dbi-owner]
GO
ALTER USER [dbiservicech\dbi-reader] WITH NAME = [dbiservicecorp\dbi-reader], LOGIN=[dbiservicecorp\dbi-reader]
GO


– “With Name = ” change the name inside the database to be the same as the login
– “With Login = “ change the SID to be the same as the login

Et voila, with this simple command, you can easily migrate the database-users to a new domain without touching the permissions.  😎