运行旧版本存储过程的 SQL Server

问题描述

我们有 1 个用户，当他们从 VB 应用程序运行存储过程时，它运行的是旧版本的 SP.旧版本是指被存储过程更新覆盖的版本.

We have 1 user that when they run a stored procedure from a VB application, it runs an old version of the SP. By old version, I mean the version that was overwritten by updates to the stored procedure.

• 我们只有 1 个架构 (dbo)
• 我在服务器上的任何其他数据库(包括主数据库)中检查了相同的 SP，它只存在一次
• 我们确实使用 NT Auth
• 我使用 SQL Profiler 来确保调用了正确的 SP.

• 我什至通过在 BEGIN 之后对第一行的 sp 进行以下更改来对此进行测试:

• We only have 1 schema (dbo)
• I checked for the same SP in any other database (including master) on the server and it only exists once
• We do use NT Auth
• I used SQL Profiler to make sure the right SP was being called and it was.

• I even tested this by making the following change to the sp on the first line after BEGIN:

raiserror('这是更新后的 SP 有错误！',16,1)

raiserror('This is the updated SP with an error!',16,1)

返回

该用户不会收到此错误，而是收到原始错误.他们得到的错误并不重要，因为它已被修复，但就像这 1 个用户正在调用不同的 SP.

This user does not get this error, they instead get the original error. The error they get is not important because it has been fixed but it is like this 1 user is calling a different SP.

更令人困惑的是，几个月前我们遇到了同样的问题，使用不同的数据库和 vb 应用程序以及 2 个不同的用户.我们为解决他们的问题所做的是将它们从 Active Director 中删除，然后使用不同的名称添加它们.

To makes thing more confusing, we had the same issue a few months ago with a different database and vb app and 2 different users. What we did to fix their issue is remove them from active director and then add them with a different name.

有没有人知道可能会发生什么，我可以尝试其他方法而不是重新创建用户，或者有没有其他人遇到过这个问题?请告诉我我没有疯.

Does anyone have any idea of what might be happening, something else I could try instead of recreating the user, or has anyone else ever ran across this? Please tell me I am not insane.

EDIT:我们在 VB 应用程序和 SQL Server 中更改了 SP 的名称并观察 SQL Profiler，它确实运行重命名的 SP，但它仍然运行在SP.所有代码都被删除了，唯一存在的是 Raiserror ......肯定有我们遗漏的东西.

EDIT: We changed the name of the SP in both the VB app and in SQL Server and watching SQL Profiler, it does run the renamed SP but it still runs the old code that was in the SP. All code has been removed and the only thing that exists is the Raiserror... There has to be something we are missing.

EDIT2:似乎添加到 SP 的可选 BIT 参数与此有关.以下是几个月前更改前 SP 的样子:

EDIT2: Would appear that an optional BIT paramater added to the SP has something to do with this. Here is what the SP looked like a few months ago before a change:

ALTER PROCEDURE [dbo].[BulkLoadSomeData]
    @UserName varchar(50),
    @FileName as varchar(max),
    @OriginalFileName as varchar(max)
AS
BEGIN
  SET NOCOUNT ON;
  BULK INSERT ....
  ...Process the data...
END

现在:

ALTER PROCEDURE [dbo].[BulkLoadSomeData]
  @UserName varchar(50),
  @FileName as varchar(max),
  @OriginalFileName as varchar(max),
  @HasElevatedSecurity bit = 0
AS
BEGIN
  SET NOCOUNT ON;
  IF @HasElevatedSecurity = 0 BEGIN
    ...Stick this into a process queue to run with higher priviledges...
    ...code ommited...
    RETURN --Must return so we dont run the rest of the code
  END  
  BULK INSERT ....    
  ...Process the data...
END

所以我们在SET NOCOUNT ON;"之后的那一行添加了raiserror('This is the updated SP with an error!',16,1)"并且用户仍然收到关于无法访问 BULK INSERT 的错误，但其他所有人都收到了我们提出的错误.

So we added "raiserror('This is the updated SP with an error!',16,1)" on the line after "SET NOCOUNT ON;" and the user still got the error about not having access to BULK INSERT but everyone else got the error we were raising.

然后我创建了一个包含这四个参数的表，并用一些插入 SQL 替换了 RAISERROR.一个用户收到 BULK INSERT 错误并且表中没有记录，其他所有人都插入记录并运行该过程而没有错误.在 SQL Profiler 中，所有的 exec 语句都是一样的.

Then I created a table that has these four paramanters in them and replaced the RAISERROR with some insert SQL. The one user gets the BULK INSERT error and no record in the table, everyone else inserts the record and runs the process without error. In SQL Profiler, all the exec statements are the same.

顺便说一句，SQL Profiler 显示:

BTW, SQL Profiler shows this:

exec BulkLoadSomeData @UserName='User1', @FileName='UNC Path and file name with no special characters', @OriginalFileName='Line the other file name'

推荐答案

根据我们在 EDIT2 下看到的存储过程代码和附加信息，我们知道:

Based on the Stored Proc code and additional information that we see under EDIT2 we know that:

1. 正在调用批量插入
2. 用户仍然收到无法访问 BULK INSERT 的错误，但其他所有人都收到了我们提出的错误"

某些 T-SQL 函数(例如 OPENQUERY、OPENROWSET、BULK INSERT 等)对安全性进行预验证.用户必须具有 INSERT 和 ADMINISTER BULK OPERATIONS 权限，在某些情况下还必须具有 ALTER TABLE，才能执行 BULK INSERT.此外，将验证用户的 NTFS/Active Directory 权限(如果使用 Windows 身份验证)或 SQL Server 服务的登录身份"帐户(如果使用 SQL Server 身份验证)以确保文件可读.

Certain T-SQL functions (e.g. OPENQUERY, OPENROWSET, BULK INSERT, etc) do pre-validation on security. A User must have INSERT and ADMINISTER BULK OPERATIONS permissions, and in some cases ALTER TABLE, in order to execute BULK INSERT. Additionally, NTFS / Active Directory permissions for the User (if using Windows Authentication) or the "Log On As" Account of the SQL Server Service (if using SQL Server Authentication) will be validated to ensure that the file is readable.

预验证(或至少我所说的预验证")发生在调用存储过程(或函数等)时，而不是在执行每一行时.如果此时发生错误，则不会执行 Proc 中的任何代码，包括 RAISERROR 或 INSERT 到日志表中.

Pre-validation (or at least what I am calling "pre-validation") occurs when the Stored Proc (or Function, etc) is called and not as each line is executed. If an error occurs at this point then none of the code in your Proc will be execute, including the RAISERROR or the INSERT into the log table.

因此，您所看到的行为最可能的原因是出现问题的用户缺乏 a) 一个或多个所需的 SQL Server 权限，或 b) 适当的 NTFS 权限，或 c)以上都是.

Hence, the most likely cause of the behavior that you are seeing is that the User that has the issue is lacking either a) one or more of the required SQL Server permissions, or b) the appropriate NTFS permissions, or c) all of the above.

鉴于错误是关于无法访问 BULK INSERT，我猜测是该特定用户缺少一项或多项 SQL Server 权限.

Given that the error was about not having access to BULK INSERT, my guess is that this particular user is missing one or more of the SQL Server permissions.

这篇关于运行旧版本存储过程的 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！