locked
What happens when you delete a linked server? RRS feed

  • Question

  • What actually happens when you deleted a linked server? SSMS says deleting a remote server deletes all remote logins defined for that server..Are you sure you want to delete this linked server and all remote logins defined for this server? 

    How can I see the remote logins for the linked server?

    thanks in advance

    Tuesday, February 10, 2015 1:21 AM

Answers

  • It will ONLY delete objects on the local server - nothing is ever changed on the remote server when you define or delete a Linked Server.

    When you create a Linked Server it stores the connection information along with any defined login mappings that you create.  When you delete it, it deletes the login mappings and then the connection information.

    • Proposed as answer by Olaf HelperMVP Tuesday, February 10, 2015 7:50 AM
    • Marked as answer by BaanLucy Tuesday, February 10, 2015 12:57 PM
    Tuesday, February 10, 2015 2:42 AM
  • It will delete only the login mappings defined in the linked server object and does NOT delete any logins defined on the destination server.

    The login mappings (the remote logins) cabe seen in the Security tab of the linked server object properties from SSMS --> Server Objects --> Linked Servers --> Select the linked server and right click and select the properties --> go to Security Tab


    Satish Kartan www.sqlfood.com

    • Proposed as answer by Olaf HelperMVP Tuesday, February 10, 2015 7:50 AM
    • Marked as answer by BaanLucy Tuesday, February 10, 2015 12:33 PM
    Tuesday, February 10, 2015 1:57 AM
  • so when I go to the security tab -- i don't see any local logins... as it was created with remote login sa;

    if i do the delete linked server... the remote logoin sa will NOT be deleted from the destination server... is that correct?  thanks for helping me

    That's correct - remote sa won't be deleted.

    Satish Kartan www.sqlfood.com

    • Marked as answer by BaanLucy Tuesday, February 10, 2015 12:33 PM
    Tuesday, February 10, 2015 2:24 AM
  • So this is the problem with using the UI - you don't really know what is going on under the hood.

    You can see your linked servers in sys.servers. You can see mapped logins in sys.linked_logins.

    You drop a server with sp_dropserver, and if I remember correctly it also drops the logins.

    Nothing is ever touched on the remote data source. it is a purely local operation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by BaanLucy Tuesday, February 10, 2015 1:02 PM
    Tuesday, February 10, 2015 11:33 AM

All replies

  • It will delete only the login mappings defined in the linked server object and does NOT delete any logins defined on the destination server.

    The login mappings (the remote logins) cabe seen in the Security tab of the linked server object properties from SSMS --> Server Objects --> Linked Servers --> Select the linked server and right click and select the properties --> go to Security Tab


    Satish Kartan www.sqlfood.com

    • Proposed as answer by Olaf HelperMVP Tuesday, February 10, 2015 7:50 AM
    • Marked as answer by BaanLucy Tuesday, February 10, 2015 12:33 PM
    Tuesday, February 10, 2015 1:57 AM
  • so when I go to the security tab -- i don't see any local logins... as it was created with remote login sa;

    if i do the delete linked server... the remote logoin sa will NOT be deleted from the destination server... is that correct?  thanks for helping me

    Tuesday, February 10, 2015 2:00 AM
  • so when I go to the security tab -- i don't see any local logins... as it was created with remote login sa;

    if i do the delete linked server... the remote logoin sa will NOT be deleted from the destination server... is that correct?  thanks for helping me

    That's correct - remote sa won't be deleted.

    Satish Kartan www.sqlfood.com

    • Marked as answer by BaanLucy Tuesday, February 10, 2015 12:33 PM
    Tuesday, February 10, 2015 2:24 AM
  • It will ONLY delete objects on the local server - nothing is ever changed on the remote server when you define or delete a Linked Server.

    When you create a Linked Server it stores the connection information along with any defined login mappings that you create.  When you delete it, it deletes the login mappings and then the connection information.

    • Proposed as answer by Olaf HelperMVP Tuesday, February 10, 2015 7:50 AM
    • Marked as answer by BaanLucy Tuesday, February 10, 2015 12:57 PM
    Tuesday, February 10, 2015 2:42 AM
  • So this is the problem with using the UI - you don't really know what is going on under the hood.

    You can see your linked servers in sys.servers. You can see mapped logins in sys.linked_logins.

    You drop a server with sp_dropserver, and if I remember correctly it also drops the logins.

    Nothing is ever touched on the remote data source. it is a purely local operation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by BaanLucy Tuesday, February 10, 2015 1:02 PM
    Tuesday, February 10, 2015 11:33 AM