在SQL Server中获取用户名的DDL触发器

Question

我有一个客户的“谁在什么时候对哪个表做了什么”的需求。系统的简要描述如下：

• 没有软件的源代码
• 软件不跟踪事件
• 所有用户都使用“sa”登录名使用应用程序
• 数据库中有一个登录表，在应用程序启动期间验证用户凭据
• 使用SQL认证，而非Windows认证。无法在SQL中创建单独的用户/角色，因为前端到后端的连接字符串是硬编码的，不可编辑

我的问题如下：

• 我可以获取主机名、IP、Suser_Name()、MAC地址等信息。但客户对它们都不满意。
• 我可以将IP地址映射到用户登录表。但客户还是不满意。

我的问题是：

有没有办法将登录表中的用户名与DDL触发器关联起来？登录表有2列，用户名和密码。这里是否有一些连接/其他操作可能被忽略了？

系统运行在SQL Server 2008上。

- Rizzy

---

我非常确定，基于您的设置，答案是不可能的。如果登录例程更新了用户登录表（例如最后登录日期值），则您可能可以使用CONTEXT_INFO和该表上的触发器来做些事情，但由于您的登录表仅具有两列，应用程序显然仅读取这些列以验证用户身份，我认为您没有机会。您尝试的将IP映射到用户登录，或获取HostName/IP/SUser_Name/MAC地址详细信息的方法，可能是客户端能够获得的最好方法。 - MartW

在您的应用程序登录期间，将SPID - 用户名信息保存在另一个表中。在触发器中，根据SPID检索并获取用户名。 - Squirrel

@MartW，看起来我运气不好。感谢您的评论。 - Rizzy

@Squirrel，我会尝试一下。我猜我必须为此创建一个触发器，因为我无法编辑从前端到后端的任何命令。 - Rizzy

登录表是否与AD链接？ - Yuval Perelman

Answer 1

你也许可以追踪user/password表中的select语句，然后根据spid和时间范围进行连接。

https://learn.microsoft.com/en-us/sql/relational-databases/sql-trace/sql-trace

• 使用 sp_trace_create 创建跟踪。
• 使用 sp_trace_setevent 添加项目事件。在这种情况下，Batch Complete 应该可以工作。
• 使用 sp_trace_setfilter 设置过滤器。将其过滤到您的 user/password 表。
• 使用 sp_trace_setstatus 启动跟踪。
• 使用 sp_trace_setstatus 停止跟踪。
• 使用 sp_trace_setstatus 关闭跟踪。

https://learn.microsoft.com/en-us/sql/relational-databases/sql-trace/create-and-run-traces-using-transact-sql-stored-procedures

- e-Fungus

---

我很想尝试这种方法，但我不再使用上述程序了。这个问题是在2016年提出的，我找不到“关闭”它的方法。尽管如此，这似乎是正确的方法；我不知道我们可以使用这些事件来玩弄服务器分析器。 - Rizzy