监视 SQL Server 2005 的帐户活动
我正在寻找最简单的方法来查看哪些用户正在登录我的数据库。我们有一些可能不再使用的旧用户帐户。我认为可能有某种方法可以监控谁登录并在下个月左右运行某种类型的查询，而不是只是将其关闭并查看谁抱怨。监视和跟踪此类活动的最简单方法是什么？
编辑： 我想对服务器上的所有数据库执行此操作。
I'm looking for the easiest way to view what users are logging into my database. We have some old user accounts that might not be getting used anymore. Instead of just turning them off and seeing who complains, I thought there might be some way to monitor who logs in and runs some type of query over the next month or so. What would be the easiest way to monitor and track this kind of activity?
Edit:
I would like to do this for all databases on the server.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
要查看谁正在连接，您可以使用登录触发器 允许您登录访问。如果您进行故障转移、重新启动 SQL 等，运行一两个月的跟踪来审核登录事件可能根本不起作用。
但是，要查看连接后某人在做什么，那么您真的必须像 Mitch 所说的那样使用 Profiler
To see who's connecting, you can use Logon Triggers which allows you to log access. Running a trace for a month or 2 to audit login events may simply not work if you failover, restart SQL etc
However, to see what someone is doing after connection, then you'll really have to use Profiler like Mitch said
选择审核登录事件运行探查器跟踪：或仅选择标准跟踪模板（并且可能限制跟踪大小）。
请参阅使用 SQL Server Profiler
Run a profiler trace with the Audit Login event selected: or just select the Standard Trace Template (and perhaps limit the trace size).
See Using SQL Server Profiler
最简单的方法是使用定制的第三方工具来为您完成这项工作。否则，您必须忙于（不是 SQL Profiler，而是）跟踪、定期加载结果数据并对其进行处理，就我而言，这并不是一件“容易”的事情。
没有太大帮助。我发帖的原因是，仅仅因为某人（或某物）一天、一周或一个月没有登录，并不意味着该帐户已经被废弃——我只会认为这是一个迹象。我建议您一旦确定其可能已被废弃，然后将其禁用并看看会发生什么。在实际删除之前，给它一个月、一个季度甚至一年（取决于您的系统）。
（当然，在一个月/一个季度/一年内跟踪该信息会更加麻烦和麻烦。理想情况下，所有帐户都是使用停用/删除规则创建的，并且他们的用户/所有者都被告知他们访问该帐户的规则。这现在可能对您没有帮助，但在您设计的下一个系统中请记住这一点。）
The easiest way to do this would be with a third-party tool that's custom-written to do the work for you. Otherwise you have to fuss with (not SQL Profiler but) traces, regularly loading resulting data, and processing it, and for my money, that just is not an "easy" thing to do.
Not much help. The reason I'm posting is that just because someone (or something) hasn't logged in for a day, a week, or a month, does not mean that the account has gone derelict--I would only consider it an indication. I would recommend that once you've identified it as potentially derelict, then you disable it and see what happens. Give that a month, a quarter, or even a year (depends on your system) before actually deleting it.
(Of course, tracking that information over a month/quarter/year is yet more fuss and bother. Ideally, all accounts get created with deactivation/deletion rules, and their users/owners are informed of the rules under which they get to access the system. This probably won't help you now, but keep it in mind for the next system you design.)