获取 SQL Server 中的当前连接属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/968760/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting current connection properties in SQL Server
提问by Tom
In MS SQL Server, the Database Properties dialog has the "View Connection Properties" link over on the left. Clicking that brings the "Connection Properties" dialog with properties of the current connection, such as Authentication Method, Network Protocol, Computer Name, etc...
在 MS SQL Server 中，“数据库属性”对话框的左侧有“查看连接属性”链接。单击它会显示“连接属性”对话框，其中包含当前连接的属性，例如身份验证方法、网络协议、计算机名称等...
Is there a way to get that information programmatically by running a sql query? What would that query look like?
有没有办法通过运行 sql 查询以编程方式获取该信息？该查询会是什么样的？
回答by Remus Rusanu
SQL 2005 and after you interrogate sys.dm_exec_connections. To retrieve your current connection properties you'd run:
SQL 2005 和您询问之后sys.dm_exec_connections。要检索您当前的连接属性，您将运行：
select * from sys.dm_exec_connections
where session_id = @@SPID
The field values depend on the protocol used to connect (shared memory, named pipes or tcp) but all contain information about authentication method used, protocol and client net address.
字段值取决于用于连接的协议（共享内存、命名管道或 tcp），但都包含有关使用的身份验证方法、协议和客户端网络地址的信息。
回答by adrianbanks
Yes you can, but it depends on which property you are after as the ones displayed in the connection properties UI come from several places.
是的，您可以，但这取决于您所追求的属性，因为连接属性 UI 中显示的属性来自多个位置。
It uses several queries (such as xp_msverand select suser_sname()) to get hold of some properties, but it also uses the xp_instance_regreadstored procedure to get hold of some values from the registry of the server.
它使用多个查询（例如xp_msver和select suser_sname()）来获取某些属性，但它也使用xp_instance_regread存储过程来获取服务器注册表中的某些值。
Pretty much everything that is done is management studio when interacting with the SQL engine can be done using SQL. Starting a profiler session and doing the actions in the UI will uncover what (sometimes obscure/undocumented/unsupported) SQL is being run.
当与 SQL 引擎交互时，几乎所有完成的都是 management studio 可以使用 SQL 完成。启动分析器会话并在 UI 中执行操作将发现正在运行的（有时是模糊的/未记录的/不受支持的）SQL。
回答by Steven
I think the answer is 'no'. Computer information is stored on the computer. Connection information is most likely stored in a configuraton file or in MS SQL Server.
我认为答案是否定的。计算机信息存储在计算机上。连接信息很可能存储在配置文件或 MS SQL Server 中。
But have a look at the MSSQL system tablesand see what properties they have.
但是看看MSSQL 系统表，看看它们有什么属性。
回答by Lukasz Szozda
From client tool perspective you could use CONNECTIONPROPERTY:
从客户端工具的角度来看，您可以使用CONNECTIONPROPERTY：
For a request that comes in to the server, this function returns information about the connection properties of the unique connection which supports that request.
对于进入服务器的请求，此函数返回有关支持该请求的唯一连接的连接属性的信息。
SELECT ConnectionProperty('net_transport') AS [Net transport],
ConnectionProperty('protocol_type') AS [Protocol type];
