Pau Lo
asked on
linked servers and account
I am trying to do some risk assessment work to determine any risks posed by current linked servers. I have the output of sys.linked_logins, sys.servers and sys.server_principals which seem to be the main tables containig information about the links. But can anyone give any pointers on determining under which account our instance is connecting to the remote instance, and vice versa. Somewhere there must be clues as to which account is used in the link?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so remote_name indicates the account in which you are connecting to the remote instance?
Right. And Local Login "Uses Self Credentials" indicates that the same credentials as the logged in user will be used to connect to the remote server.
Here is the SQL (for future reference):
SELECT ss.server_id
,ss.name
,'Server ' = Case ss.Server_id
when 0 then 'Current Server'
else 'Remote Server'
end
,ss.product
,ss.provider
,ss.catalog
,'Local Login ' = case sl.uses_self_credential
when 1 then 'Uses Self Credentials'
else ssp.name
end
,'Remote Login Name' = sl.remote_name
,'RPC Out Enabled' = case ss.is_rpc_out_enabled
when 1 then 'True'
else 'False'
end
,'Data Access Enabled' = case ss.is_data_access_enabled
when 1 then 'True'
else 'False'
end
,ss.modify_date
FROM sys.Servers ss
LEFT JOIN sys.linked_logins sl
ON ss.server_id = sl.server_id
LEFT JOIN sys.server_principals ssp
ON ssp.principal_id = sl.local_principal_id
ASKER
Just seen the SQL so looks like I can use the data I already have as its using those tables in the script you linked to.