The Wiert Corner – irregular stream of stuff

Sometimes in a less paved SQL Server environment you need a quick way to gather information on both the client and server. I assembled this query from various sources to help with that. It runs with few privileges (hence the use of the various *property functions):

-- https://dev-doc.blogspot.com/2012/08/ms-sql-2008-client-ip-address-on-shared.html
-- https://blog.sqlauthority.com/2009/05/26/sql-server-find-hostname-and-current-logged-in-user-name/
-- https://blog.sqlauthority.com/2015/07/13/sql-server-how-to-change-server-name/
-- https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6720817d-120f-4099-bf0e-e97fd2e26848/how-to-get-host-name-and-sql-instance-name-by-tsql?forum=transactsql#fc9e6b84-0264-424a-8aef-d03b0de6fade
select
  -- https://docs.microsoft.com/en-us/sql/t-sql/functions/connectionproperty-transact-sql?view=sql-server-2017
  CONNECTIONPROPERTY('net_transport') AS net_transport,
  CONNECTIONPROPERTY('protocol_type') AS protocol_type,
  CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
  CONNECTIONPROPERTY('local_net_address') AS local_net_address,
  CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
  CONNECTIONPROPERTY('client_net_address') AS client_net_address,
  HOST_NAME() AS client_hostname,
  SUSER_NAME() LoggedInUser,
  @@servername AS 'ServerName\InstanceName',
  -- https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-2017
  SERVERPROPERTY('ServerName') AS 'ServerName',
  SERVERPROPERTY('MachineName') AS 'Windows_MachineName',
  SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name',
  SERVERPROPERTY('instanceName') AS 'InstanceName',
  SERVERPROPERTY('IsClustered') AS 'IsClustered',
  SERVERPROPERTY('Edition') AS 'Edition',
  -- https://docs.microsoft.com/en-us/sql/t-sql/functions/version-transact-sql-configuration-functions?view=sql-server-2017
  @@Version as 'Full_Server_Version'
;

Based on parts from:

• [WayBack] Заметки разработчика: MS SQL 2008: Client IP address on shared hosting
• [WayBack] SQL SERVER – Find Hostname and Current Logged In User Name – SQL Authority with Pinal Dave
• [WayBack] SQL SERVER – How to Change Server Name? – SQL Authority with Pinal Dave
• [WayBack] How to get Host name and SQL Instance Name by T-SQL

Features used:

• [WayBack] CONNECTIONPROPERTY (Transact-SQL) | Microsoft Docs
•  [WayBack] HOST_NAME (Transact-SQL) | Microsoft Docs
•  [WayBack] SUSER_NAME (Transact-SQL) | Microsoft Docs
• [WayBack] SERVERPROPERTY (Transact-SQL) | Microsoft Docs
• [WayBack] @@VERSION (Transact-SQL) | Microsoft Docs

–jeroen

https://gist.github.com/jpluimers/1a9256882624448285719af01239f8b2