在儲存過程中選擇 sys 表的權限

我已經實現了儲存過程來檢查是否存在特定的[schema].[procedure]。

CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist] 
@schemaName varchar(128),
@procedureName varchar(128)
AS
SELECT IIF (EXISTS (SELECT 1 FROM sys.procedures p INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id] 
   WHERE s.[name] = @schemaName  AND p.[name] = @procedureName), 1, 0)
GO

當我使用sa它進行測試時，我按預期選擇了 1，但是當我嘗試使用僅授予EXECUTE該程序的最小權限帳戶時，它選擇了 0。

我對儲存過程機制的假設是角色/使用者只需要EXECUTE執行該過程。我們不需要關心每個數據庫對象的權限設置，比如 CRUD 到 table 等等。

但似乎 sys 表是例外？還是我錯過了什麼？

此外，如果我不想將 select sys.procedures/授予sys.schemas該角色。我只希望我的程序按預期工作，我該怎麼辦？

我對儲存過程機制的假設是角色/使用者只需要 EXECUTE 來執行該過程。我們不需要關心每個數據庫對象的權限設置，比如 CRUD 到 table 等等。

當所有權連結適用時，此陳述是正確的。使用完整的鏈，使用者不需要間接引用對象的權限。

但是，在從目錄視圖中進行選擇時，所有權連結不會應用元數據可見性。必須授予使用者對對象的某些權限，以便在從目錄視圖中選擇對象時可見。

下面是一個為您的案例顯示此行為的腳本。如果您需要返回使用者無權使用的對象，則需要使用EXECUTE AS證書籤名或證書來提升BlobCheckExisting.usp_DoesStoredProcedureExist過程使用者的權限。

CREATE SCHEMA BlobCheckExisting;
GO

CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist] 
@schemaName varchar(128),
@procedureName varchar(128)
AS
SELECT IIF (EXISTS (SELECT 1 FROM sys.procedures p INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id] 
   WHERE s.[name] = @schemaName  AND p.[name] = @procedureName), 1, 0)
GO

CREATE PROCEDURE dbo.usp_TestProc AS RETURN;
GO

CREATE LOGIN MinimallyPrivilegedUser WITH PASSWORD = 'sdf4$$$gFx&';
GO

CREATE USER MinimallyPrivilegedUser;
GO

GRANT EXECUTE ON BlobCheckExisting.usp_DoesStoredProcedureExist TO MinimallyPrivilegedUser;
GO

--test permissions without permissions on object
EXECUTE AS USER = 'MinimallyPrivilegedUser';
GO
--no rows returned because user has no permissions on dbo.usp_TestProc
EXEC [BlobCheckExisting].[usp_DoesStoredProcedureExist] @schemaName = N'dbo', @procedureName = N'usp_TestProc';
GO
REVERT;
GO
--grant permissions
GRANT EXECUTE ON dbo.usp_TestProc TO MinimallyPrivilegedUser;
GO

--test permissions with permissions on object
EXECUTE AS USER = 'MinimallyPrivilegedUser';
GO
--row returned because user has execute permissions on dbo.usp_TestProc
EXEC [BlobCheckExisting].[usp_DoesStoredProcedureExist] @schemaName = N'dbo', @procedureName = N'usp_TestProc';
GO
REVERT;
GO

引用自：https://dba.stackexchange.com/questions/267884