Tuesday, November 06, 2012

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'

No comments: