SQL Script to List all Linked Server Logins and their properties

Leave a comment (0) Go to comments

During migration, there was an requirement to list down all link Server with respective properties to figure out what all objects will be required in new Server. Thus a Script is required is required, which can list down all linked Servers and associated properties.

The properties listed are

  • Server
  • Product 
  • Provider
  • Catalog 
  • Local Login
  • Remote Login Name
  • RPC out Enabled and
  • Data Access Enabled
  • Object creation date

Also this lists the local login and remote login. This query result may have repeated linked server name if it has more than one associated remote login.

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

OUTPUT


SQL Script to List all Linked Server Logins and their properties script to get remote logins used in linked servers List all Link Servers Linked Server Logins and their properties

Note : SQL script will get the list of Linked server with its properties and its associated local/Remote login list.

EOF - SQL Script to List all Linked Server Logins and their properties, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.