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

Answers

  • Hi PhotoHiker,

    I am sorry that I had an incorrect understanding for your above posting. I hope that  it's right this time.

     

    'test1'  is a login on the local server and it is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access. For more details , please refer to sp_addlinkedsrvlogin (Transact-SQL).

     

    And sp_dropserver will remove existing mappings between a login on the local server running SQL Server and a login on the linked server instead of local login and then remove a server from the list of known remote and linked servers on the local instance of SQL Server.

     

    And you can see the changes of linked  login from following script. Please try it.

     
    exec sp_addlinkedserver @server='RemoteSvr', @srvproduct='SQL Server' 
    select * from sys.linked_logins where server_id =1
    /*
    server_id   local_principal_id uses_self_credential remote_name                                                                                                                      modify_date
    ----------- ------------------ -------------------- -------------------------------------------------------------------------------------------------------------------------------- -----------------------
    1           0                  1                    NULL                                                                                                                             2019-01-22 15:51:34.113
    */
    Go
    
    EXEC master.dbo.sp_addlinkedsrvlogin 
    	@rmtsrvname = N'RemoteSvr', 
    	@locallogin = N'***', 
    	@useself = N'False', 
    	@rmtuser = N'test2',
    	@rmtpassword = N'Password0'
    
    GO
    select * from sys.linked_logins where server_id =1
    /*
    server_id   local_principal_id uses_self_credential remote_name                                                                                                                      modify_date
    ----------- ------------------ -------------------- -------------------------------------------------------------------------------------------------------------------------------- -----------------------
    1           0                  1                    NULL                                                                                                                             2019-01-22 15:51:34.113
    1           259                0                    test2                                                                                                                            2019-01-22 15:53:18.397
    */
    
    -----sp_dropserver
    EXEC sp_dropserver
       @server = N'RemoteSvr',
       @droplogins = 'droplogins'
    select * from sys.linked_logins where server_id =1
     /*
     server_id   local_principal_id uses_self_credential remote_name                                                                                                                      modify_date
    ----------- ------------------ -------------------- -------------------------------------------------------------------------------------------------------------------------------- -----------------------
    
     */
    

     

    Hope it can help you .

     

    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.

    • Marked as answer by PhotoHiker Tuesday, January 22, 2019 2:56 PM
    Tuesday, January 22, 2019 8:41 AM

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.


    • Edited by PhotoHiker Friday, January 18, 2019 3:47 PM
    Friday, January 18, 2019 3:36 PM
  • 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

    Friday, January 18, 2019 6:51 PM
  • 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

    I didn't talk about sp_addlinksrvlogin.

    I noticed the logins were not dropped from local instance when using @droplogins='droplogins'. it is not aligned with the statement ".... remove .... all associated remote logins from the local instance of SQL Server" in screenshot of my previous post. I want to know why they are different ? and which is right?

    Monday, January 21, 2019 3:50 PM
  • I noticed the logins were not dropped from local instance when using @droplogins='droplogins'. it is not aligned with the statement ".... remove .... all associated remote logins from the local instance of SQL Server" in screenshot of my previous post. I want to know why they are different ? and which is right?

    "Droplogins" specifies that any login mappings set up for the linked server should be removed. (If you don't specify "droplogins', and there are login mappings, sp_dropsever fails.) That is, you dropped the mapping set up for the local login test1, but the local login test1 continues to exist.

    Just in the same vein, if you grant permissoins to test1 on a table and drop the table, the permissions for test1 are dropped, but the login/user test1 is not dropped.


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

    Monday, January 21, 2019 10:28 PM
  • Hi PhotoHiker,

    I am sorry that I had an incorrect understanding for your above posting. I hope that  it's right this time.

     

    'test1'  is a login on the local server and it is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access. For more details , please refer to sp_addlinkedsrvlogin (Transact-SQL).

     

    And sp_dropserver will remove existing mappings between a login on the local server running SQL Server and a login on the linked server instead of local login and then remove a server from the list of known remote and linked servers on the local instance of SQL Server.

     

    And you can see the changes of linked  login from following script. Please try it.

     
    exec sp_addlinkedserver @server='RemoteSvr', @srvproduct='SQL Server' 
    select * from sys.linked_logins where server_id =1
    /*
    server_id   local_principal_id uses_self_credential remote_name                                                                                                                      modify_date
    ----------- ------------------ -------------------- -------------------------------------------------------------------------------------------------------------------------------- -----------------------
    1           0                  1                    NULL                                                                                                                             2019-01-22 15:51:34.113
    */
    Go
    
    EXEC master.dbo.sp_addlinkedsrvlogin 
    	@rmtsrvname = N'RemoteSvr', 
    	@locallogin = N'***', 
    	@useself = N'False', 
    	@rmtuser = N'test2',
    	@rmtpassword = N'Password0'
    
    GO
    select * from sys.linked_logins where server_id =1
    /*
    server_id   local_principal_id uses_self_credential remote_name                                                                                                                      modify_date
    ----------- ------------------ -------------------- -------------------------------------------------------------------------------------------------------------------------------- -----------------------
    1           0                  1                    NULL                                                                                                                             2019-01-22 15:51:34.113
    1           259                0                    test2                                                                                                                            2019-01-22 15:53:18.397
    */
    
    -----sp_dropserver
    EXEC sp_dropserver
       @server = N'RemoteSvr',
       @droplogins = 'droplogins'
    select * from sys.linked_logins where server_id =1
     /*
     server_id   local_principal_id uses_self_credential remote_name                                                                                                                      modify_date
    ----------- ------------------ -------------------- -------------------------------------------------------------------------------------------------------------------------------- -----------------------
    
     */
    

     

    Hope it can help you .

     

    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.

    • Marked as answer by PhotoHiker Tuesday, January 22, 2019 2:56 PM
    Tuesday, January 22, 2019 8:41 AM