如何授予權限以允許 SQL Server 架構中的任何內容？

我有一個名為training. 我有一個新角色，叫做training_modify. 以下是賦予該角色的使用者所需的權限：

• 對該架構中的任何對象的選擇、更新、插入和刪除權限
• 在該架構中創建任何對象
• 刪除該架構中的任何對象
• public對該架構之外的任何其他對像沒有權限（通過角色授予的權限除外
• 架構本身沒有創建/刪除/更改權限
• 角色和個人使用者不應要求具有db_ddladmin、db_datareader、db_datawriter或任何其他預設安全形色的成員身份

簡單來說，我希望角色中的使用者能夠在架構內做他們想做的任何事情，而不會影響/看到架構或架構本身之外的任何內容。授予此類訪問權限的最低權限是什麼？

到目前為止我的方法：

• 以所有者身份創建training架構dbo
• 以所有者身份創建training_modify角色dbo
• 將架構上的所有權限授予角色
• 將創建對象授予角色
• 將使用者添加到角色

以下是上述步驟的程式碼：

CREATE SCHEMA training AUTHORIZATION dbo;

CREATE ROLE training_modify AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
         UPDATE, VIEW DEFINITION ON SCHEMA::training TO training_modify;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO training_modify;

EXEC sp_addrolemember 'training_modify', 'example_user';

這是正確的方法嗎？它有什麼意想不到的後果嗎？我最擔心所有權連結如何對這種方法產生負面影響，dbo因為它是模式和角色（以及數據庫中的其他模式/對象）的所有者，以及對模式授予的更改權限。

還：

• 還應該dbo擁有這個角色嗎？還是應該TestOwner同時擁有角色和架構？
• 如果我取消CREATE PROCEDUREandCREATE FUNCTION權限，這是否會阻止特定模式之外的對像上的任何 DML 是否準確？我認為我不關心他們是否可以創建過程，儘管我希望他們能夠執行過程/功能（我認為這將包含EXECUTE在架構上的權限中）。如果這是準確的 - 這種方法與更改架構所有者之間是否有任何優點/缺點？

所有權連結通常不用擔心。它僅暗示 DML（INSERT、UPDATE和DELETE）SELECT、 和EXECUTE操作的權限。它不允許CREATE, ALTER,DROP等。

這裡棘手的部分/細微差別是，預設情況下，對像有一個NULL所有者，這意味著它們的所有權隱含為它們所在模式的所有者。因此，在這種特殊情況下，使用：

1. training架構由,dbo和…
2. training_modify使用者能夠在模式中創建儲存過程和/或函式training，

training_modify不能對錶執行 DML的使用者dbo.可以簡單地創建training.[proc]執行 DML 的使用者，然後執行該儲存過程，它將允許對dbo.錶執行 DML。

更改training架構的所有者可以解決此問題（假設您不希望訪問dbo.對象）。您可以創建一個僅限數據庫的使用者（即WITHOUT LOGIN）並使該使用者成為training模式的所有者。該使用者不會用於其他任何事情；它的存在只是為了允許模式的擁有principal_id與training模式的擁有不同dbo，從而打破所有權連結。

請注意：

• CREATE ROLE的文件指出：

角色的所有者或擁有角色的任何成員都可以添加或刪除角色的成員。

含義：角色的所有者不影響所有權連結。但是，training_modify角色中的任何人都可能無法添加/刪除其他人，因此最好保留角色dbo的所有者（而不是模式）。

• ALTER AUTHORIZATION的文件在“特殊情況和條件”下指出：

當所有權轉移時，沒有明確所有者的包含模式的對象的權限將被刪除。

含義：在執行架構後，您將需要對架構的GRANT權限。ALTER AUTHORIZATION

• 至於兩者之間的優缺點，這在很大程度上取決於dbo模式中將存在哪些對象、如何訪問它們、訓練應該完成什麼以及這種設置需要多麼靈活。我不知道任何這些資訊，所以現在我將簡單概括地說：

  • 架構所有者是dbo並且不允許CREATE PROCEDURE：

    1. CON：培訓人員不能玩創建過程和功能
    2. ???：如果培訓人員需要訪問dbo.對象，則可以在training模式中創建模組（CON，如果需要大量對象和/或不同的操作，否則只是“meh”）。

  • 更改架構所有者並允許CREATE PROCEDURE：

    1. PRO：培訓人員可以玩創建過程和功能
    2. ???：如果培訓人員需要訪問dbo.對象，則需要向training_modify角色授予顯式權限，允許對這些對象 (CON) 進行臨時訪問，或者需要將模組添加到dbo模式中以執行該訪問和EXECUTE/SELECT權限可以授予這些（CON，如果需要很多對象和/或不同的操作，否則只是“meh”）。假設架構中沒有太多dbo.和/或所需的訪問權限相對簡單，我會選擇更改架構所有者並允許CREATE PROCEDURE等。

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