เกิดข้อผิดพลาดเมื่อใช้ OPENROWSET จาก View as a nonsysadmin

Question

[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 และสร้าง rowset กระบวนงานที่จัดเก็บยังเรียกขั้นตอนการจัดเก็บเพิ่มเติมดังนั้นนี่จึงไม่ใช่สิ่งที่แปลงเป็นฟังก์ชันได้อย่างง่ายดาย

วิธีเดียวในปัจจุบันของฉันในการทำให้สิ่งนี้ใช้งานได้ในบัญชีที่สร้างขึ้นใหม่คือการให้สิทธิ์ผู้ดูแลระบบ เห็นได้ชัดว่านี่ไม่ใช่เส้นทางที่ต้องการเพื่อเหตุผลด้านความปลอดภัย แต่ช่วยยืนยันว่าควรใช้งานได้

มีคำถามมากมายเกี่ยวกับ SQL Server + OPENROWSET แต่จากการค้นคว้าข้อมูลไม่กี่ชั่วโมงฉันดูเหมือนจะไม่พบวิธีแก้ปัญหาเฉพาะสำหรับกรณีนี้ ความช่วยเหลือใด ๆ จะได้รับการชื่นชมมาก

ฉันมาถึงการตั้งค่าปัจจุบันของฉันผ่านการอ้างอิงคำตอบดังนี้:

• https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table
• https://stackoverflow.com/questions/2896445/why-can-we-not-execute-a-stored-procedure-inside-a-function-in-sql-server
• https://stackoverflow.com/questions/916784/how-to-call-stored-procedure-in-a-view?noredirect=1&lq=1

และอื่น ๆ อีกมากมาย.

หมายเหตุตอนท้ายฉันได้พิจารณาแล้วว่ามีขั้นตอนการจัดเก็บอัปเดต / สร้างตารางในเครื่องซึ่งอาจทำงานตามกำหนดเวลาทุกคืนเพื่อให้แน่ใจว่าข้อมูลเป็นข้อมูลล่าสุดและสร้างมุมมองโดยอิงจากค่าในตารางที่อัปเดตเหล่านั้น อย่างไรก็ตามจะช่วยลดรหัสที่ซ้ำกันได้มากและการกำหนดค่าโพรซีเดอร์ที่จัดเก็บใหม่หากฉันสร้างมุมมองเพื่อเรียกใช้โพรซีเดอร์ที่จัดเก็บได้ทันทีด้วยพารามิเตอร์ที่ฉันต้องการส่งไปยังโพรซีเดอร์

Answer 1

เพื่อตอบสนองต่อคำถามของฉันเองก็สามารถที่จะได้รับมันทำงานโดยใช้เซิร์ฟเวอร์ที่เชื่อมโยงย้อนกลับและใช้ 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)
                )
            )
        ')