問題描述

我們有 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的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！