Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
From the current connection, without any special rights, get client and host information including connection protocol and addresses
-- 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'
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment