存储过程被随机丢弃 [英] Stored Procedure gets dropped randomly

问题描述

我们最近在暂存和生产环境中遇到了一个奇怪的问题.我们在 SQL Server 2005 上运行了一个存储过程的更新脚本，验证了新的更改，并开始在我们的产品上使用它.一段时间后，同一个存储过程从数据库中消失了.除了我们打算使用的任务之外，该存储过程没有被任何其他任务使用.我们检查了每一个代码和部署脚本，但找不到只是删除存储过程的痕迹.

We have recently encountered a strange problem on our staging and production environments. We have run an update script to a stored procedure on SQL Server 2005, verified the new change, and started using it on our products. After sometime the same stored procedure has gone missing from the DB. This stored procedure is not being used by any other tasks except the one we are intending to use. We have checked every bit of code and deployment script, but cannot find a trace for just dropping the stored procedure.

此问题不会出现在我们的 DEV 和 QA 环境中，而只会出现在暂存和生产环境中.

This issue doesn't occur on our DEV and QA environments, but on Staging and Production only.

有人可以帮忙吗?

亲切的问候，

马法兹

推荐答案

如果您已经排除了明显的(例如故意破坏)，那么我建议您查看一下 sys.sql_modules 参考该过程 -可能是意外滑倒，例如:

If you've ruled out the obvious (e.g. deliberate sabotage), then I would suggest having a look through sys.sql_modules for a reference to the procedure - possibly there is an accidental slip like:

IF NOT EXISTS (SELECT 1 FROM SYS.PROCEDURES WHERE NAME = 'Proc1')
  DROP PROCEDURE Proc1
GO

CREATE PROC dbo.Proc1
  AS
    ...

<< MISSING GO!

IF NOT EXISTS (SELECT 1 FROM SYS.PROCEDURES WHERE NAME = 'Proc2')
  DROP PROCEDURE Proc2
GO

CREATE PROC dbo.Proc2
  AS
  ...

即在上面，DROP PROCEDURE Proc2 代码被附加INTO 无关Proc1 的定义，因为缺少GO> 在 Proc1 定义的末尾.每次运行 Proc1 时，它都会删除 proc Proc2(如果 Proc2 将不方便地隐藏错误，Proc2> 已被删除).

i.e. in the Above, the DROP PROCEDURE Proc2 code gets appended INTO the definition of the unrelated Proc1 because of a missing GO at the end of the Proc1 definition. Every time Proc1 is run, it will drop proc Proc2 (and the if exists will inconveniently hide an error if Proc2 is already dropped).

同样，另一个常见问题是将 GRANT EXEC 留在 PROC 的底部 - 如果权限不严，这会破坏程序的性能.

Similarly, another common issue is leaving GRANT EXEC at the bottom of the PROC - if permissions are lax, this can destroy the performance of a procedure.

这里最好的建议是用最少的权限执行应用程序，这样它就不能执行DDL，例如DROP 或GRANT.这样，当 Proc1 执行时，应用程序就会崩溃，让您可以快速追踪有问题的代码.

The best advice here is to execute applications with minimal permissions, so that it is not able to execute DDL such as DROP or GRANT. This way, the app will break when Proc1 is executed, allowing you to quickly track down the offending code.

这篇关于存储过程被随机丢弃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！