Drop Linked Server in SQL Server

You have learned how to create linked server in SQL Server in Accessing Oracle From SQL Server. Sometimes you may want to drop it. I'll show you how.

First find and drop the logins associated with the linked server.
user master
go

select s.srvid, s.srvname, l.name login_name_associated
from dbo.sysxlogins x, dbo.syslogins l, dbo.sysservers s
where l.sid = x.sid and s.srvid = x.srvid and s.srvname = 'ora_test'
go
srvid srvname login_name_associated
----- --------- ------------------------
1 ora_test sa
1 ora_test analyst

Now sa and analyst need to be dropped from the linked server.

sp_droplinkedsrvlogin @rmtsrvname = 'ora_test', @locallogin = 'analyst'
go
sp_droplinkedsrvlogin @rmtsrvname = 'ora_test', @locallogin = 'sa'
go

Then the linked server is ready to be dropped.

sp_dropserver @server = 'ora_test'
go

The logins have to be dropped from linked server first, otherwise an error message will show:

There are still remote logins for the server 'ora_test'.