Unable to change servername after VM Clone

Question

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?

Answer 1

You need to check both sys.remote_logins and sys.linked_logins. You should be able to build a script dynamically to drop the set you find:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'EXEC sys.sp_droplinkedsrvlogin @s, N''' + remote_name + N''''
  FROM sys.linked_logins
  WHERE server_id = 0 AND remote_name IS NOT NULL;

SELECT @sql += N'EXEC sys.sp_dropremotelogin @s, N''' + remote_name + N''''
  FROM sys.remote_logins
  WHERE server_id = 0 AND remote_name IS NOT NULL;

PRINT @sql; -- spot check will only show first 8K of command

DECLARE @s NVARCHAR(512) = N'old server name';
--EXEC sys.sp_executesql @sql, N'@s NVARCHAR(512)', N'.\SQL2014';

Answer 2

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.