問題描述

我們有 1 個(gè)用戶，當(dāng)他們從 VB 應(yīng)用程序運(yùn)行存儲(chǔ)過程時(shí)，它運(yùn)行的是舊版本的 SP.舊版本是指被存儲(chǔ)過程更新覆蓋的版本.

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 個(gè)架構(gòu) (dbo)
• 我在服務(wù)器上的任何其他數(shù)據(jù)庫(包括主數(shù)據(jù)庫)中檢查了相同的 SP，它只存在一次
• 我們確實(shí)使用 NT Auth
• 我使用 SQL Profiler 來確保調(diào)用了正確的 SP.

• 我什至通過在 BEGIN 之后對第一行的 sp 進(jìn)行以下更改來對此進(jìn)行測試:

• 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 有錯(cuò)誤！',16,1)

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

返回

該用戶不會(huì)收到此錯(cuò)誤，而是收到原始錯(cuò)誤.他們得到的錯(cuò)誤并不重要，因?yàn)樗驯恍迯?fù)，但就像這 1 個(gè)用戶正在調(diào)用不同的 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.

更令人困惑的是，幾個(gè)月前我們遇到了同樣的問題，使用不同的數(shù)據(jù)庫和 vb 應(yīng)用程序以及 2 個(gè)不同的用戶.我們?yōu)榻鉀Q他們的問題所做的是將它們從 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.

有沒有人知道可能會(huì)發(fā)生什么，我可以嘗試其他方法而不是重新創(chuàng)建用戶，或者有沒有其他人遇到過這個(gè)問題?請告訴我我沒有瘋.

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 應(yīng)用程序和 SQL Server 中更改了 SP 的名稱并觀察 SQL Profiler，它確實(shí)運(yùn)行重命名的 SP，但它仍然運(yùn)行在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 參數(shù)與此有關(guān).以下是幾個(gè)月前更改前 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

現(xiàn)在:

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)"并且用戶仍然收到關(guān)于無法訪問 BULK INSERT 的錯(cuò)誤，但其他所有人都收到了我們提出的錯(cuò)誤.

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.

然后我創(chuàng)建了一個(gè)包含這四個(gè)參數(shù)的表，并用一些插入 SQL 替換了 RAISERROR.一個(gè)用戶收到 BULK INSERT 錯(cuò)誤并且表中沒有記錄，其他所有人都插入記錄并運(yùn)行該過程而沒有錯(cuò)誤.在 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'

推薦答案

根據(jù)我們在 EDIT2 下看到的存儲(chǔ)過程代碼和附加信息，我們知道:

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

1. 正在調(diào)用批量插入
2. 用戶仍然收到無法訪問 BULK INSERT 的錯(cuò)誤，但其他所有人都收到了我們提出的錯(cuò)誤"

某些 T-SQL 函數(shù)(例如 OPENQUERY、OPENROWSET、BULK INSERT 等)對安全性進(jìn)行預(yù)驗(yàn)證.用戶必須具有 INSERT 和 ADMINISTER BULK OPERATIONS 權(quán)限，在某些情況下還必須具有 ALTER TABLE，才能執(zhí)行 BULK INSERT.此外，將驗(yàn)證用戶的 NTFS/Active Directory 權(quán)限(如果使用 Windows 身份驗(yàn)證)或 SQL Server 服務(wù)的登錄身份"帳戶(如果使用 SQL Server 身份驗(yàn)證)以確保文件可讀.

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.

預(yù)驗(yàn)證(或至少我所說的預(yù)驗(yàn)證")發(fā)生在調(diào)用存儲(chǔ)過程(或函數(shù)等)時(shí)，而不是在執(zhí)行每一行時(shí).如果此時(shí)發(fā)生錯(cuò)誤，則不會(huì)執(zhí)行 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.

因此，您所看到的行為最可能的原因是出現(xiàn)問題的用戶缺乏 a) 一個(gè)或多個(gè)所需的 SQL Server 權(quán)限，或 b) 適當(dāng)?shù)?NTFS 權(quán)限，或 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.

鑒于錯(cuò)誤是關(guān)于無法訪問 BULK INSERT，我猜測是該特定用戶缺少一項(xiàng)或多項(xiàng) SQL Server 權(quán)限.

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.

這篇關(guān)于運(yùn)行舊版本存儲(chǔ)過程的 SQL Server的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！