Created
October 20, 2018 10:18
-
-
Save jpluimers/1a9256882624448285719af01239f8b2 to your computer and use it in GitHub Desktop.
From the current connection, without any special rights, get client and host information including connection protocol and addresses
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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