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?
LVL 3
pma111Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Harish VargheseConnect With a Mentor Project LeaderCommented:
There is a script available at below link that lists all linked servers on current server and the logins associated with them. Please check:
http://gallery.technet.microsoft.com/scriptcenter/Get-List-of-Linked-Server-d6c95d9c

-Harish
0
 
jayakrishnabhConnect With a Mentor Commented:
Accounts used for the linked server can be found from the linkedserver properties window or create script of that
0
 
pma111Author Commented:
Cant I use the information in the tables I already have as mentioned above?

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.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
pma111Author Commented:
so remote_name indicates the account in which you are connecting to the remote instance?
0
 
Harish VargheseProject LeaderCommented:
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.
0
 
Harish VargheseProject LeaderCommented:
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

Open in new window

0
All Courses

From novice to tech pro — start learning today.