Get the security configuration of Linked Servers out like SSMS

Question

I need to query the list of Linked Server and make sure that they all have their security setting on For a login not defined in the list above, connections will:'Not be maid' (The first radio option).

Answer 1

It just so happens that every option is a twisted combination of a multitudes of fields.

-- Representation of Linked Server GUI
-- {3} represent the third option on the GUI:Be made using the login's current security context
-- This way you can filter out some configuration that are considered too loose security wise.

use master
go
select l.server_id, s.name
    , case 
        when l.uses_self_credential = 0 and p.name is not null          then 'top: Local server login to remote server login mapping'
        when l.uses_self_credential = 1 and p.name is not null          then 'top: Local server login to impersonate'
        when l.uses_self_credential = 0 and l.remote_name = ''          then '{2} Be made without using a security context'
        when l.uses_self_credential = 1 and l.remote_name is null       then '{3} Be made using the login''s current security context'
        when l.uses_self_credential = 0 and l.remote_name is not null   then '{4} Be made using this security context'
      end as detail
    , l.uses_self_credential, p.name as local_name, l.remote_name, l.modify_date
    from sys.linked_logins l
    join sys.servers s on s.server_id = l.server_id
    left join sys.server_principals p on p.principal_id = l.local_principal_id
union all
    select server_id, name, '{1} Not be made',0 ,null , null, null
    from sys.servers
    where server_id not in(select server_id from sys.linked_logins where local_principal_id = 0)
order by name