Sql-Server

以非系統管理員身份從視圖使用 OPENROWSET 時出錯

  • August 19, 2020

$$ SQL Server 2012 $$我遇到了一個關於使用非系統管理員帳戶呼叫視圖的問題,該視圖包含從儲存過程中提取的 OPENROWSET 呼叫。我想知道是否有人對我目前正在嘗試的內容有可能的解決方案,或者有一個建議可以讓我擺脫在視圖中使用 OPENROWSET 的目前解決方法(可能是連結伺服器設置?)。 我目前正在呼叫一個如下所示的過程:

SELECT *
FROM
OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=NO;UID=accountName;PWD=accountPassword;', 
          'SET FMTONLY OFF; 
           SET NOCOUNT ON;
           EXEC [My Stored Procedure] @params = ''field1,field2''
           WITH RESULT SETS
           (
               (
                   field1 NVARCHAR(1000),
                   field2 NVARCHAR(1000)
               )
           )
       ')

並將結果返回到視圖中。這一切都很好,並在以系統管理員身份執行時執行。問題是我們現在希望授予非系統管理員使用者訪問此視圖的權限,但是,在授予使用者對該視圖的 SELECT 權限並嘗試以該使用者身份訪問該視圖後,出現此錯誤:

'Access to the remote server is denied because no login-mapping exists.'

我可能已將其範圍縮小到視圖本身呼叫 OPENROWSET 的事實。使用 OPENROWSET 是因為我呼叫了從 API 中提取數據並生成行集的儲存過程。儲存過程還呼叫擴展儲存過程,因此這不是很容易轉換為函式的東西。

在新創建的帳戶中,我目前唯一讓它工作的方法是授予它系統管理員權限。出於安全原因,這顯然不是理想的路線,但它有助於確認這應該有效。

關於 SQL Server + OPENROWSET 有大量問題,但基於幾個小時的研究,我似乎無法找到針對這種情況的解決方案。任何幫助將不勝感激。

我通過參考以下答案得出了我目前的設置:

還有很多。

結束註釋,我已經考慮讓儲存過程更新/創建本地表,可能每晚執行以確保數據是最新的,並根據這些更新表中的值創建視圖。但是,如果我創建視圖以使用我希望發送到過程的參數動態呼叫儲存過程,它會減少很多重複程式碼和儲存過程的重新配置。

為了回答我自己的問題,能夠使用環回連結伺服器並使用 OPENQUERY 而不是 OPENROWSET使其工作。非系統管理員帳戶現在具有所需的受控訪問權限。

這是視圖使用的新查詢:

SELECT *
FROM
OPENQUERY([LOOPBACKSERVERNAME], 
          'SET FMTONLY OFF; 
           SET NOCOUNT ON;
           EXEC [My Stored Procedure] @params = ''field1,field2''
           WITH RESULT SETS
           (
               (
                   field1 NVARCHAR(1000),
                   field2 NVARCHAR(1000)
               )
           )
       ')

引用自:https://dba.stackexchange.com/questions/273936