谁删除了我的 sql 表行？
我有一个 SQL 表，正在从中删除数据。没有人知道数据是如何被删除的。我添加了一个触发器并知道时间，但是没有正在运行的作业会删除数据。每当从此表中删除行时，我还添加了一个触发器。然后，我将删除的行和 SYSTEM_USER 插入到日志表中，但这并没有多大帮助。我可以采取什么更好的措施来了解谁以及如何删除数据？是否可以获取服务器ID之类的？感谢您的任何建议。
抱歉：我正在使用 SQL Server 2000。
**更新 1*：了解数据如何被删除很重要 - 最好我想知道正在执行的 DTS 包或 SQL 语句。
I have an SQL table, from which data is being deleted. Nobody knows how the data is being deleted. I added a trigger and know the time, however no jobs are running that would delete the data. I also added a trigger whenever rows are being deleted from this table. I am then inserting the deleted rows and the SYSTEM_USER to a log table, however that doesnt help much. Is there anything better I can do to know who and how the data gets deleted? Would it be possible to get the server id or something? Thanks for any advice.
Sorry: I am using SQL Server 2000.
**update 1*: Its important to find out how the data gets deleted - preferably I would like to know the DTS package or SQL statement that is being executed.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
只是猜测，但是您是否在其中一个父表（由外键引用的表）上有删除级联。如果是这样，当您删除父行时，子表中的条目也会被删除。
Just a guess, but do you have delete cascades on one of the parent tables (those referenced by foreign keys). If so, when you delete the parent row the entries in the child table are also removed.
如果恢复模式设置为“完整”，您可以检查日志。
除此之外，删除对该表的任何删除授权。如果仍然发生这种情况，则说明执行此操作的人具有 root/dbo 访问权限 - 因此请更改密码...
If the recovery mode is set to "Full", you can check the logs.
Beyond that, remove any delete grants to the table. If it still happens, whomever is doing it has root/dbo access - so change the password...
尝试暂时记录所有事务，即使这会损害性能。 MS 提供了一个 mssql 分析器，如果需要的话还包括 Express 版本。有了它，您应该能够记录交易。作为探查器的替代方法，您可以使用trace_build存储过程将活动转储到参考文件中，然后只需对单词“delete”或其他类似关键字的任何实例使用“ctrl-f”即可。有关详细信息，请参阅此页面...
日志记录SQL Server 2008 Express 数据库上的所有查询？
另外，这可能听起来很愚蠢，但请调查您所看到的不是删除的可能性。相反，调查记录是否只是被“更新”、“如果已存在则替换”、“更新插入”或任何您喜欢的称呼。在 Mysql 中，这是“INSERT ... ON DUPLICATE KEY UPDATE”语句。我不确定 MSSQL 的变体。
Try logging all transactions for the time being, even if if it hurts performance. MS offers a mssql profiler, including for express versions if needed. With it, you should be able to log transactions. As an alternative to profilers, you can use the trace_build stored procedure to dump activity into reference files, then just 'ctrl-f' for any instance of the word 'delete' or other similar keywords. For more info, see this SO page...
Logging ALL Queries on a SQL Server 2008 Express Database?
Also, and this may sound stupid, but investigate the possibility that what you are seeing is not deletes. Instead, investigate if records are simply being 'updated', 'replaced if already exists', 'upserted', or whatever you like to call it. In Mysql, this is the 'INSERT ... ON DUPLICATE KEY UPDATE' statement. I'm not sure of the MSSQL variant.
您的数据库采用什么恢复模式？如果它已满Redgate log Rescue是免费的并且适用于SQL2000可能会帮助您找回已删除的数据。 概览视频确实似乎显示用户栏。
或者您可以针对 fn_dblog 发起自己的查询
What recovery model is your database in? If it is full Redgate log Rescue is free and works against SQL2000 which might help you retrieve the deleted data. The Overview Video does appear to show a user column.
Or you could roll your own query against fn_dblog
更改所有密码。给予尽可能少的人删除访问权限。
Change all your passwords. Give as few people delete access as possible.