拒絕對角色的 DDL_ADMIN 權限dbodb這dbo圖式

我有幾個數據庫模式，比如，

$$ ext $$,$$ stag $$等，當然還有內置的$$ dbo $$schema，並且還有一個角色叫做$$ MyRole $$. 我的計劃是添加

$$ MyRole $$到 DDL_ADMIN 角色，所以他們可以在所有模式中創建、更改、刪除對象，但我希望阻止他們在$$ dbo $$架構。 DDL_ADMIN 內置角色為其成員提供以下權限：

ALTER ANY ASSEMBLY                    
ALTER ANY ASYMMETRIC KEY              
ALTER ANY CERTIFICATE                 
ALTER ANY CONTRACT                    
ALTER ANY DATABASE DDL TRIGGER        
ALTER ANY DATABASE EVENT NOTIFICATION 
ALTER ANY DATASPACE                   
ALTER ANY FULLTEXT CATALOG            
ALTER ANY MESSAGE TYPE                
ALTER ANY REMOTE SERVICE BINDING      
ALTER ANY ROUTE                       
ALTER ANY SCHEMA                      
ALTER ANY SERVICE                     
ALTER ANY SYMMETRIC KEY               
CHECKPOINT                            
CREATE AGGREGATE                      
CREATE DEFAULT                        
CREATE FUNCTION                       
CREATE PROCEDURE                      
CREATE QUEUE                          
CREATE RULE                           
CREATE SYNONYM                        
CREATE TABLE                          
CREATE TYPE                           
CREATE VIEW                           
CREATE XML SCHEMA COLLECTION          
REFERENCES                            

由於 DENY 優先於 GRANT，因此我可以在dbo架構上拒絕完全相同的權限

$$ MyRole $$. 它應該很簡單：

DENY ALTER ANY ASSEMBLY                    ON SCHEMA::dbo TO MyRole
DENY ALTER ANY ASYMMETRIC KEY              ON SCHEMA::dbo TO MyRole
DENY ALTER ANY CERTIFICATE                 ON SCHEMA::dbo TO MyRole
DENY ALTER ANY CONTRACT                    ON SCHEMA::dbo TO MyRole
DENY ALTER ANY DATABASE DDL TRIGGER        ON SCHEMA::dbo TO MyRole
DENY ALTER ANY DATABASE EVENT NOTIFICATION ON SCHEMA::dbo TO MyRole
DENY ALTER ANY DATASPACE                   ON SCHEMA::dbo TO MyRole
DENY ALTER ANY FULLTEXT CATALOG            ON SCHEMA::dbo TO MyRole
DENY ALTER ANY MESSAGE TYPE                ON SCHEMA::dbo TO MyRole
DENY ALTER ANY REMOTE SERVICE BINDING      ON SCHEMA::dbo TO MyRole
DENY ALTER ANY ROUTE                       ON SCHEMA::dbo TO MyRole
DENY ALTER ANY SCHEMA                      ON SCHEMA::dbo TO MyRole
DENY ALTER ANY SERVICE                     ON SCHEMA::dbo TO MyRole
DENY ALTER ANY SYMMETRIC KEY               ON SCHEMA::dbo TO MyRole
DENY CHECKPOINT                            ON SCHEMA::dbo TO MyRole
DENY CREATE AGGREGATE                      ON SCHEMA::dbo TO MyRole
DENY CREATE DEFAULT                        ON SCHEMA::dbo TO MyRole
DENY CREATE FUNCTION                       ON SCHEMA::dbo TO MyRole
DENY CREATE PROCEDURE                      ON SCHEMA::dbo TO MyRole
DENY CREATE QUEUE                          ON SCHEMA::dbo TO MyRole
DENY CREATE RULE                           ON SCHEMA::dbo TO MyRole
DENY CREATE SYNONYM                        ON SCHEMA::dbo TO MyRole
DENY CREATE TABLE                          ON SCHEMA::dbo TO MyRole
DENY CREATE TYPE                           ON SCHEMA::dbo TO MyRole
DENY CREATE VIEW                           ON SCHEMA::dbo TO MyRole
DENY CREATE XML SCHEMA COLLECTION          ON SCHEMA::dbo TO MyRole
DENY REFERENCES                            ON SCHEMA::dbo TO MyRole
GO

SQL Server 對以上所有內容都說“DENY ALTER …附近的語法不正確”。

我試圖從 BOL 中拼出正確的語法，但只能想出：

DENY ALTER ON SCHEMA::dbo TO MyRole;

沒有其他的。

拒絕 DDL_ADMIN 成員資格提供給的所有權限的最簡單方法是什麼

$$ MyRole $$成員，但僅限於$$ dbo $$架構？ 謝謝！

您嘗試更改的某些權限在您嘗試使用它們的方式中並不存在。

例如，要拒絕CREATE TABLE和大多數其他 DDL 語句，對於特定模式，正確的語法和權限應該是DENY ALTER ON SCHEMA::dbo TO MyRole.

這應該涵蓋相當於 db_ddladmin 角色的權限。（我知道這有點不直覺，但拒絕所有ALTER，就像我上面的例子一樣，確實包括拒絕CREATE權限。）

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