0

First, my SQL Server user didn't have sysadmin permissions, and I want to get client port like

SELECT client_tcp_port 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;

How can I get this information with T-SQL?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lzx4627
  • 81
  • 1
  • 5
  • https://stackoverflow.com/questions/12297475/how-to-find-sql-server-running-port – Yitzhak Khabinsky Nov 01 '21 at 13:02
  • Thanks for your answer, but this answer is all need sysadmin, my dbuser is Low permissions user without sys permissions – Lzx4627 Nov 01 '21 at 13:06
  • If you're connected then your client app will know which port it's on. Why would you want to do this in TSQL? – Charlieface Nov 01 '21 at 13:33
  • The client drivers don't expose this information directly, so you would have to enumerate all the sockets connected to the SQL Server's IP address. And the server could have multiple, or for in Azure SQL Database connecting from Azure be a redirection from the load balancer. – David Browne - Microsoft Nov 01 '21 at 13:44
  • The link I provided, shows how to identify the post at the command prompt too. – Yitzhak Khabinsky Nov 01 '21 at 13:56
  • In fact, all of the above methods require administrator privileges,So i changed the plan https://stackoverflow.com/questions/69807068/how-to-synchronize-the-contents-of-the-system-view-to-my-own-table-in-real-time – Lzx4627 Nov 02 '21 at 08:42

1 Answers1

1

Either get a sysadmin to grant you the required VIEW SERVER STATE permission, or provide you with a signed stored procedure.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67