Unable to change servername after VM Clone

For reasons out of my control I must find a solution to this issue. Simply reinstalling the instance is not an option. The server name that appears as server_id 0 in sys.servers is still showing the old servername.

I'm getting an error when running the below command:

sp_dropserver 'OLD_INSTANCE'
GO
sp_addserver 'NEW_INSTANCE', Local
GO

Error message:


Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 67

There are still remote logins or linked logins for the server 'OLD_INSTANCE'.

Msg 15028, Level 16, State 1, Procedure sp_addserver, Line 87

The server 'NEW_INSTANCE' already exists.

The strangest thing is, the remote login is a 'null' login.

exec sp_dropremotelogin @remoteserver = 'OLD_INSTANCE'
go

Error message:


Msg 15185, Level 16, State 1, Procedure sp_dropremotelogin, Line 70

There is no remote user '(null)' mapped to local user '(null)' from the remote server 'OLD_INSTANCE'.

No logins exist for the old instance.

sp_helpremotelogin 'OLD_INSTANCE'

Msg 15201, Level 16, State 1, Procedure sp_helpremotelogin, Line 37

There are no remote logins for the remote server 'OLD_INSTANCE'.

How do I rename this instance if I can't drop the non-existing login? Is there a way to flush the logins?

I had to post, because this gave me the most help today in renaming a SQL2014 default instance...

You can also use the following:

exec sp_dropserver 'current_name', @droplogins = 'droplogins'
exec sp_addserver 'new_name', local

This absolutely saved my bacon today. Thank you for this post, and I hope that mine can help others, also.

Context: had a failover from SQL2008 to a new installation of SQL2014. There are a few production SQL machines that are linked as per our software provider's specs.

I was trying to rename the 2014 server to match the IP and name of the 2008 server (already changed to something different), but kept getting the error for remote/linked logins existing.

After a LOT of searching, the @droplogins command worked.

I hope this helps someone in the future.

StackExchange Database Administrators Q#122823, answer score: 5