Confusion on SQL Server domain\username after Windows changing hostname

Question

We have a SQL Server 2016 running on a virtual machine of Windows 2016. Previously, the virtual machine's hostname was WIN when we installed the SQL Server, so the database engine's server name was WIN and the usernames were like WIN\Administrator.

Later, we changed the virtual machine's hostname to MSSQLV, and the database's server name automatically changed to MSSQLV, e.g., when connection with Management Studio on the same host, the "Windows Authentication" shows MSSQLV\Administrator.

However, when we check the database's properties, in the context of Select the database > right click > Properties > General > Database > Owner, the Owner property still shows WIN\Administrator.

We tried to correct the Owner by the SQL command ALTER AUTHORIZATION ON DATABASE::[database] to [MSSQLV\Administrator];, and it got a successful running result. But the Owner property stays the same as before.

Moreover, we checked the context of Computer Management (Local) > System Tools > Local Users and Groups > Groups, and found the following two names still quoting the old hostname WIN:

• SQLServer2005SQLBrowserUser$WIN
• SQLServerMSASUser$WIN$MSSQLSERVER

The Concern:

We highly suspect this confusion caused a usability issue being not able to get the ER-Diagram of the database with the following error message:

Could not obtain information about Windows NT group/user 
'WIN\Administrator', error code 0x534. (Microsoft SQL Server, 
Error: 15404)

We have posted a separate question for the specific issue above.

Our Question:

Within the existing instance of SQL Server, how to change the domain\username from WIN\xxxxx to MSSQLV\xxxxx to make the domain match the new hostname?

Please also kindly remind us of if we missed other aspects of settings after hostname changing. And we highly appreciate any hints and suggestions.

Answer 1

However, when we check the database's properties, in the context of Select the database > right click > Properties > General > Database > Owner, the Owner property still shows WIN\Administrator.

Yes, that's how it'll show since the login (and any users) didn't have their names changed in the base metadata tables. Renaming a server doesn't change any of that information nor does it change the SID of the local account.

We tried to correct the Owner by the SQL command ALTER AUTHORIZATION ON DATABASE::[database] to [MSSQLV\Administrator];, and it got a successful running result. But the Owner property stays the same as before.

The name will correctly resolve, locally, to the SID for that login which is tied by SID to the originally used name which is WIN\Administrator which is what should show in sys.server_principals.

Moreover, we checked the context of Computer Management (Local) > System Tools > Local Users and Groups > Groups, and found the following two names still quoting the old hostname WIN

Yes, these are local groups. The group name doesn't need to change, it's just a name. The group is only and can only be a local group, so technically the full name of the group did change from WIN\ to MSSQLV\ but that part isn't displayed.

Could not obtain information about Windows NT group/user 'WIN\Administrator', error code 0x534. (Microsoft SQL Server, Error: 15404)

This is because you won't be able to lookup the local account by using an account that has a different name even if the SID is the same. The Windows API to look up a name and get the SID will fail to look up the local account starting with WIN\ because that computer name no longer exists. The error code also tells you (indirectly) as much: No mapping between account names and security IDs was done.

The first thing I would do is update the login metadata to be correct for the new name using ALTER LOGIN. This may or may not fix the saved metadata for the diagrams, as that is saved differently as part of attributes which is more a SSMS thing that an engine thing. If that doesn't work, just remove them and regenerate them.