.net 如何判断谁登录到 SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/935791/
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
How to tell who is logged in to SQL Server
提问by Jeff Stock
I am designing an application and I'm looking for the best way to tell who is currently logged into SQL server. I will use the Activity Monitor built into SSMS, which shows a list of all processes accessing the server - very cool.
我正在设计一个应用程序，我正在寻找告诉谁当前登录到 SQL 服务器的最佳方法。我将使用 SSMS 内置的活动监视器，它显示访问服务器的所有进程的列表 - 非常酷。
However, if in my .NET code it uses a separate connection each time I access the database then how will I be able to tell who is currently logged in? If a user is just looking at a screen and not retrieving data from the database at that moment then they wouldn't show up correct?
但是，如果在我的 .NET 代码中每次访问数据库时它都使用单独的连接，那么我将如何知道当前登录的人？如果用户当时只是看着屏幕而不是从数据库中检索数据，那么他们就不会正确显示？
回答by
In MSSQL server you can run the following command...
在 MSSQL 服务器中，您可以运行以下命令...
exec sp_who2
This will give you a lot of information including who is logged in, application name, current command, so on and so forth.
这将为您提供很多信息，包括谁登录、应用程序名称、当前命令等等。
Thanks
谢谢
回答by Michael Petrotta
Maybe, maybe not. You're correct that being logged into your application does not require a user to be logged into the database - in fact, that concept doesn't really exist. Activity Montitor (and, usefully, sp_who2) will show only active connections - those connections actively in use.
也许，也许不是。您是正确的，登录到您的应用程序不需要用户登录到数据库 - 事实上，这个概念并不存在。活动监视器（以及有用的 sp_who2）将仅显示活动连接 - 那些正在使用中的连接。
That picture changes if you use pooled connections ("Pooled=true", I believe, in your connection string). Do that, and a "closed" client connection will remain active, and you'll see that connection in the monitor. This articlehas some more detail on that.
如果您使用池连接（“Pooled=true”，我相信，在您的连接字符串中），该图片会发生变化。这样做，“关闭”的客户端连接将保持活动状态，您将在监视器中看到该连接。 这篇文章对此有更详细的介绍。
If I were you, though, I'd architect a different way to track active users, one that doesn't rely on pooled connections.
不过，如果我是你，我会设计一种不同的方式来跟踪活跃用户，一种不依赖于池连接的方式。
回答by Jonathan Kehayias
The DMV sys.dm_exec_sessions can be used to find connected sessions. You can join it to sys.dm_exec_requests to get request information if it is actively running, or to get connection specific information you can join to sys.dm_exec_connections. The session_id is used for all of the joins.
DMV sys.dm_exec_sessions 可用于查找连接的会话。如果它正在运行，您可以将其加入 sys.dm_exec_requests 以获取请求信息，或者您可以加入 sys.dm_exec_connections 以获取特定于连接的信息。session_id 用于所有连接。
I'd personally use DMV's over the older sp_who stored procedures. As others have pointed out, if the connection closes, you won't see it so if you want to track between connections, you'd have to consider another method of caching the connection information.
我个人会在旧的 sp_who 存储过程上使用 DMV。正如其他人指出的那样，如果连接关闭，您将看不到它，因此如果您想在连接之间进行跟踪，则必须考虑另一种缓存连接信息的方法。
回答by Paulo Santos
I think you're looking for sp_whoor sp_who2
我想你正在寻找sp_who或sp_who2
回答by C. Ross
Your assertion is correct, users not performing database actions would not be visible. You will really need to store some sort of "Logged In Users" information manually.
您的断言是正确的，不执行数据库操作的用户将不可见。您确实需要手动存储某种“登录用户”信息。
回答by Scott Ewers
You are correct. If coded correctly, the connection will be created when data is saved or retrieved, and closed immediately afterward.
你是对的。如果编码正确，将在保存或检索数据时创建连接，然后立即关闭。
Assuming you are more interested in who is logged into the application than who is logged into the database server, you could persist session information in the database, including user ids. Then you could simply query the session database to discover who is online. There may be better solutions depending on the development technologies you are using.
假设您对谁登录到应用程序比谁登录到数据库服务器更感兴趣，您可以将会话信息保存在数据库中，包括用户 ID。然后您可以简单地查询会话数据库以发现谁在线。根据您使用的开发技术，可能会有更好的解决方案。
