none
drop linked server with "droplogins"

    Question

  • I run the query below to create a Linked Server (called 'RemoteSvr') with mapped logins.

    
    use master
    
    exec sp_addlinkedserver @server='RemoteSvr', @srvproduct='SQL Server' 
    Go
    
    EXEC master.dbo.sp_addlinkedsrvlogin 
    	@rmtsrvname = N'RemoteSvr', 
    	@locallogin = N'test1', 
    	@useself = N'False', 
    	@rmtuser = N'test2',
    	@rmtpassword = N'Password0'
    
    GO

    Then I run command below to drop the linked server with "droplogins". The linked server 'RemoteSvr' still works fine.  I think the command should drop the linked server, but leave the mapped logins intact, but actually not. why?

    EXEC master.dbo.sp_dropserver @server=N'RemoteSvr', @droplogins=Null
    GO

    If I run the command below, the linked server was dropped, but the mapped logins "test1" and "test2" are still there, I thought they should be dropped as well. am I understanding incorrect?

    EXEC master.dbo.sp_dropserver @server=N'RemoteSvr', @droplogins='droplogins'

    Thank you.


    • Edited by PhotoHiker Friday, January 11, 2019 8:23 PM
    Friday, January 11, 2019 8:23 PM

All replies

  • That's not what I see. When I run this:

    use master
    CREATE LOGIN test1 WITH PASSWORD = 'testelitest'
    go
    exec sp_addlinkedserver @server='RemoteSvr', @srvproduct='SQL Server' 
    Go
    
    EXEC master.dbo.sp_addlinkedsrvlogin 
    	@rmtsrvname = N'RemoteSvr', 
    	@locallogin = N'test1', 
    	@useself = N'False', 
    	@rmtuser = N'test2',
    	@rmtpassword = N'Password0'
    
    GO
    EXEC master.dbo.sp_dropserver @server=N'RemoteSvr', @droplogins=Null
    
    SELECT * FROM sys.servers
    SELECT * FROM sys.linked_logins
    
    GO
    EXEC master.dbo.sp_dropserver @server=N'RemoteSvr', @droplogins='droplogins'
    
    go
    
    SELECT * FROM sys.servers
    SELECT * FROM sys.linked_logins
    
    
    go
    DROP LOGIN test1

    The first call to sp_dropserver results in an error message:

    Msg 15190, Level 16, State 1, Procedure master.dbo.sp_dropserver, Line 56 [Batch Start Line 14]
    There are still remote logins or linked logins for the server 'RemoteSvr'.

    and the server remains in sys.server and the login mapping remains in sys.linked_logins.

    The second call to sp_dropsever completes successfully, and after this call the server is not in sys.servers and there are no rows for the server in sys.linked_logins.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, January 11, 2019 10:43 PM
  • Hi PhotoHiker,

     

    To remove a linked server, form the Linked Server folder use the sp_dropserver procedure. @droplogins if the ‘droplogins’ value is set, then all logins for the specified remote server will be removed, by default the @droplogins property is set to Null. If you use NULL, as Erland said that you run sp_dropserver on a server that has associated remote and linked server login entries, or is configured as a replication publisher, an error message is returned. To resolve this problem, please remove all remote and linked server logins for a server :

     

    --->Remove all logins related to a linked server by using the sp_droplinkedsrvlogin procedure and then execute the above mentioned code.

     

    --->Instead of a Null value for the @droplogins property in the sp_dropserver procedure, just put the ‘droplogins’ value and all logins related to a chosen linked server will be removed together with a linked server.

     

    -----sp_dropserver
    EXEC sp_dropserver
       @server = N'Server',
       @droplogins = ‘droplogins’ | NULL
    
    -----sp_dropserver
    EXEC sp_droplinkedsrvlogin
       @rmtsrvname = N'Remote server',
       @locallogin = N'Local login’;


     

    For more information about 'How to create, configure and drop a SQL Server linked server using Transact-SQL', you can refer to this article: https://www.sqlshack.com/create-configure-drop-sql-server-linked-server-using-transact-sql/

     

    Best Regards,

    Rachel

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 14, 2019 6:33 AM
  • Hi PhotoHiker,

     

    To remove a linked server, form the Linked Server folder use the sp_dropserver procedure. @droplogins if the ‘droplogins’ value is set, then all logins for the specified remote server will be removed

    hi Rachel, Thanks for your response.

    I run sp_dropserver with @droplogins='droplogins', I don't see the local logins for the specified linked server are removed from Logins list, although I do see it is removed from sys.linked_logins.

    According to your response and MS DOC, I think the local logins that is associated with the linked server should be removed from local instance of SQL Server. However, I cannot get the result. Do you know why?

    Thanks.


    • Edited by PhotoHiker Tuesday, January 15, 2019 3:40 PM
    Tuesday, January 15, 2019 3:39 PM
  • I run sp_dropserver with @droplogins='droplogins', I don't see the local logins for the specified linked server are removed from Logins list, although I do see it is removed from sys.linked_logins.

    Which list are you talking about?

    If you are talking about SSMS, it may help if you provide a screen shot. (I don't use SSMS for these kind of things, so I don't really know what's in there.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, January 15, 2019 10:55 PM
  • Hi PhotoHiker,

    Thank you for your reply.

    Could you please share us your Logins list or other information ? Did you mean Window(Object Explorer)?  If so , please check if it has refreshed.

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 17, 2019 6:36 AM
  • Hi Rachel,

    Yes, I do mean the Logins in Object Explorer. Here is what I do.

    use master
    
    create login test1 with Password ='Password0'
    Go
    
    
    exec sp_addlinkedserver @server='RemoteServer', @srvproduct='SQL Server' 
    Go
    
    EXEC master.dbo.sp_addlinkedsrvlogin 
    	@rmtsrvname = N'RemoteServer', 
    	@locallogin = N'test1', 
    	@useself = N'False', 
    	@rmtuser = N'test2',
    	@rmtpassword = N'Password0'
    
    GO
    
    exec sp_dropserver @server=N'RemoteServer', @droplogins='droplogins'
    Go
    
    
    After run above scripts, the test1 still shows on Logins list in Object Explorer in SSMS.

    Thursday, January 17, 2019 3:01 PM
  • Hi PhotoHiker,

    The list in Window(Object Explorer) is not updated in real time. So if you does not refresh the  list, the test1 will be shown on Logins list in Object Explorer in SSMS.

     

    If you are sure that it is removed from sys.linked_logins , I think it is success.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 18, 2019 5:49 AM
  • Hi PhotoHiker,

    The list in Window(Object Explorer) is not updated in real time. So if you does not refresh the  list, the test1 will be shown on Logins list in Object Explorer in SSMS.

     

    If you are sure that it is removed from sys.linked_logins , I think it is success.

     

    hi Rachel,

    I did refresh the list in Object Explorer couple of times, the test1 account was still there. When you test it, was the test1 account removed from Object Explorer?

    Below is extracted from MS Document, my understanding is it means remove all logins associated with the linked server from Object Explorer. Applying to my test case, test1 should be removed from Object Explorer.


    15 hours 31 minutes ago
  • Hi Rachel,

    Yes, I do mean the Logins in Object Explorer. Here is what I do.

    use master
    
    create login test1 with Password ='Password0'
    Go
    
    
    exec sp_addlinkedserver @server='RemoteServer', @srvproduct='SQL Server' 
    Go
    
    EXEC master.dbo.sp_addlinkedsrvlogin 
    	@rmtsrvname = N'RemoteServer', 
    	@locallogin = N'test1', 
    	@useself = N'False', 
    	@rmtuser = N'test2',
    	@rmtpassword = N'Password0'
    
    GO
    
    exec sp_dropserver @server=N'RemoteServer', @droplogins='droplogins'
    Go
    
    After run above scripts, the test1 still shows on Logins list in Object Explorer in SSMS.

    Of course. It is a local login that has nothing as such to do with the linked server.

    What you so above with the call to sp_addlinksrvlogin is to say that "when test1 connects to RemoteServer, it should connect as test2". That is akin to adding test1 to a specific database (and possibly call it something else in that database). Later you may drop that database, but the login will not be dropped because of this. That would not be a good thing to do, since the login could be users in other databases.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    12 hours 17 minutes ago