Query to find the Linked Servers with given remote login

 

It would be useful to cross check, when the Linked Server remote login password has changed.
In this case, linked server will wail due to wrong password. To fix this we have reset the correct password.

SELECT s.server_id,S.name,S.product,S.provider,l.remote_name,'Remote_Login_Modified_Dt'=l.modify_date,s.modify_date FROM SYS.SERVERS S INNER JOIN SYS.LINKED_LOGINS L ON  s.server_id = L.server_id WHERE S.server_id > 0 AND S.product ='SQL Server' AND L.remote_name ='sa'