Handling Domain users when SQL Server Name and Host Name of Server hosting SQL Server is renamed

Question

The issue here is that the Host Name of the system hosting the SQL Server requires an update. After changing the hostname of the system we are also updating the SQL Server Name to the new hostname.

This results in dirty Domain user logins as they are not updated automatically by SQL Server on change of the hostname or change of the Name of SQL Server. The users can be handled one at a time by executing Alter commands. But I am searching for a way to handle all/multiple domain users at once. Any SP or SQL configuration would be great.

Answer 1

The following script should be what you need to rename all the users in the database.

I don't think the SID changes, so you shouldn't need to change the server-level login.
Otherwise you can add to this script easily.

DECLARE @sql nvarchar(max) = (
    select string_agg(CAST('
ALTER USER ' + QUOTENAME(dp.name) + ' WITH NAME = ' + QUOTENAME(REPLACE(dp.name, 'OLDSERVERNAME', 'NEWSERVERNAME'))
      AS nvarchar(max)), '
')
    from sys.server_principals dp
    where dp.type ='U'
      and dp.name LIKE 'OLDSERVERNAME\%'
);

PRINT @sql;

-- EXEC sp_executesql @sql;