This article provides an example of using Transact-SQL to remove a linked server in SQL Server.

To remove a linked server, use the sp_dropserver system stored procedure. This removes a server from the list of known remote and linked servers on the local instance of SQL Server. This stored procedure accepts two arguments: the server name, and an optional argument for removing any logins associated with the server.

Example 1 – Drop a Linked Server and its Logins

Here’s an example of dropping a linked server and all associated logins for that linked server. In this case, the linked server is called “Homer”.

EXEC sp_dropserver 'Homer', 'droplogins';

Result:

Commands completed successfully.

As with any stored procedure, you can also explicitly include the argument names. This doesn’t change the result though – the linked server is dropped along with its associated logins.

EXEC sp_dropserver 
  @server = 'Homer', 
  @droplogins = 'droplogins';

Result:

Commands completed successfully.

Example 2 – Drop a Linked Server without Dropping its Logins

As mentioned, the droplogins arguments is optional. You can also drop a linked server like this:

EXEC sp_dropserver 'Homer';

However, if there are any logins associated with the linked server, you’ll get the following error:

Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server 'Homer'.

Which means that you’ll need to add the droplogins argument.

You’ll also get an error if the linked server is configured as a replication publisher.

Again, you can explicitly include the names of the arguments if you prefer:

EXEC sp_dropserver 
  @server = 'Homer', 
  @droplogins = NULL;

Result:

Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server 'Homer'.

You won’t get an error if there are no logins associated with the linked server. For example, if you create a linked server without using sp_addlinkedsrvlogin to add a login, you’ll be able to drop the server without using the droplogins argument. This is true even if a mapping was created between your local login and one on the remote server.

So if the remote server has a login called Marge, and I run this under the Marge login on the local server:

EXEC sp_addlinkedserver 
    @server=N'Homer', 
    @srvproduct=N'', 
    @provider=N'MSOLEDBSQL', 
    @datasrc=N'172.17.0.3,1433';

A mapping is created between Marge on the local server and Marge on the linked server. This is possible because both logins share the same credentials. In this case, there’s no need to run sp_addlinkedsrvlogin to add Marge as a login for the linked server.

However, if there’s no corresponding login for Marge on the linked server (or if there is, but the credentials don’t match), the mapping won’t happen, and Marge won’t be able to access the linked server.

Either way, running the following code will be successful:

EXEC sp_dropserver 
  @server = 'Homer', 
  @droplogins = NULL;

Result:

Commands completed successfully.

This completes successfully for Marge, even when there’s a mapping between her local login and the linked server (but no other logins for the linked server).