授予對儲存過程、函式和視圖的執行權限

我正在使用僅具有讀取權限的使用者。它基本上有權限

• 連接
• 執行

但是，它沒有對函式和儲存過程的執行權限。在我的部分報告中，我呼叫了我自己的函式。我想授予我readonlyuser執行 SP、函式和視圖的權限。

我遇到了 這段程式碼，它顯示瞭如何為特定使用者授予對儲存過程和函式的執行權限。

- Grant Execute on all functions for testuser
declare @username varchar(255)  
set @username = 'testuser'  
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +  
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES  
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0   
and ROUTINE_TYPE='PROCEDURE'  

和功能

declare @username varchar(255)  
set @username = 'testuser'  
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +  
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES  
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0   
and ROUTINE_TYPE='FUNCTION'  

現在，這裡看起來這個權限只適用於現有的 SP 和功能。如果添加新功能，則必須再次執行此程式碼以授予權限。

問題

1. 如果我的上述解釋沒有錯，有沒有辦法將執行權限授予只讀使用者，以便您擁有所有函式和儲存過程的權限，即使您創建新函式？
2. 我可以對視圖應用相同的權限嗎？
3. 在這個執行緒中，據說您不能對返回表的函式授予執行權限。真的嗎？

我知道有幾個問題，但它們都是相關的。最好在一個地方。

確實，您不能授予EXEC對返回表的函式的權限。這種類型的函式實際上更像是一個視圖而不是一個函式。您需要改為授予 SELECT，例如：

GRANT SELECT ON dbo.Table_Valued_Function TO [testuser];

所以你的腳本看起來更像這樣（對不起，但我絕對討厭INFORMATION_SCHEMA並且更喜歡使用目錄視圖，它也不需要像這樣的函式OBJECTPROPERTY）：

DECLARE 
   @sql      NVARCHAR(MAX) = N'',
   @username VARCHAR(255)  = 'testuser';

SELECT @sql += CHAR(13) + CHAR(10) + N'GRANT ' + CASE 
   WHEN type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION'
     OR type_desc = 'VIEW'
   THEN ' SELECT ' ELSE ' EXEC ' END 
   + ' ON ' + QUOTENAME(SCHEMA_NAME([schema_id])) 
   + '.' + QUOTENAME(name) 
   + ' TO ' + @username + ';'
FROM sys.all_objects
WHERE is_ms_shipped = 0 AND
( 
   type_desc LIKE '%PROCEDURE' 
   OR type_desc LIKE '%FUNCTION'
   OR type_desc = 'VIEW'
);

PRINT @sql;
-- EXEC sp_executesql @sql;

現在您可以授予EXEC模式，並始終在該模式中創建這些過程（實際上是模式的目的之一！），@jgardner04 已經建議，但是為了使該解決方案也適用於表值函式，您’ 還得授予SELECT。如果您沒有在該架構中的表中儲存任何數據（或者至少您想對它們隱藏），這沒關係，但它也適用於任何表和視圖，這可能不是您的意圖。

另一個想法（例如，如果您不能或不想使用模式）是編寫一個 DDL 觸發器，該觸發器擷取CREATE_PROCEDURE、CREATE_FUNCTION和CREATE_VIEW事件，並向使用者（或一組使用者，如果您想將它們儲存在表中）：

CREATE TRIGGER ApplyPermissionsToAllProceduressAndFunctions -- be more creative!
   ON DATABASE FOR CREATE_PROCEDURE, CREATE_FUNCTION, CREATE_VIEW
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE
       @sql       NVARCHAR(MAX),
       @EventData XML = EVENTDATA();

   ;WITH x ( sch, obj ) 
   AS
   (
       SELECT
         @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
         @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)')
   )
   SELECT @sql = N'GRANT ' + CASE 
     WHEN o.type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION' 
       OR o.type_desc = 'VIEW'
     THEN ' SELECT ' 
     ELSE ' EXEC ' END 
       + ' ON ' + QUOTENAME(x.sch) 
       + '.' + QUOTENAME(x.obj) 
       + ' TO testuser;' -- hard-code this, use a variable, or store in a table
   FROM x
   INNER JOIN sys.objects AS o
   ON o.[object_id] = OBJECT_ID(QUOTENAME(x.sch) + '.' + QUOTENAME(x.obj));

   EXEC sp_executesql @sql;
END
GO

我發現 DDL 觸發器的缺點是您很快就會忘記它們的存在。因此，當您決定停止向所有新對象授予這些權限的一年後，可能需要一段時間來解決為什麼它仍然會發生。在我的上一份工作中，我們將 DDL 觸發器呼叫的所有操作記錄到各種中央“事件日誌”中，這是我們跟踪伺服器上發生的任何似乎沒人記得的操作的首選位置（這是一個DDL 觸發大約一半時間）。因此，您可以考慮添加一些有助於解決此問題的額外邏輯。

編輯

添加基於模式的程式碼，我將再次提到這將授予對在 foo 模式中創建的任何過程、函式和表的權限。

CREATE SCHEMA foo;
GRANT EXEC, SELECT ON SCHEMA::foo TO testuser;

現在，如果您創建以下過程，testuser 將能夠執行：

CREATE PROCEDURE foo.proc1
AS 
BEGIN
   SET NOCOUNT ON;
   SELECT 1;
END
GO

您可以使用模式，然後授予使用者執行模式中項目的權限嗎？可以在此處找到有關架構的更多資訊：http: //msdn.microsoft.com/en-us/library/ms187940.aspx

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