SQL Server 2008 R2 – Unable to Change Server Name After VM Clone

instancesql serversql-server-2008-r2

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?

Best Answer

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';
Related Question