4

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?

  • 1
    What do you see from SELECT * FROM sys.linked_logins WHERE server_id = 0;? (In other words, the error message says "remote logins or linked logins" and you only checked for remote logins.) – Aaron Bertrand Dec 3 '15 at 17:35
  • You're right. The linked_login exists. What is the command for getting rid of this login? – Geoff Dawdy Dec 3 '15 at 17:55
4

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';
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.