Deny DDL_ADMIN permissions to a role on [dbo] schema

Question

I have several database schemas like, [ext],[stag],etc and of course the built in [dbo] schema, and also have a role called [MyRole].

My plan is to add [MyRole] to the DDL_ADMIN role, so they can create, alter, delete objects in all schemas, but I wish to prevent them to do exactly that in the [dbo] schema.

The DDL_ADMIN built in role provides the following permissions for its members:

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                            

Since DENY takes precedence over GRANT, I can DENY exactly the same permissions above on the dbo schema for [MyRole].

It should be as simple as:

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 says "Incorrect syntax near DENY ALTER ...' for all above.

I tried to spell out the correct syntax from BOL, but could only come up with:

DENY ALTER ON SCHEMA::dbo TO MyRole;

nothing else.

What would be the easiest way to DENY all the permissions what DDL_ADMIN membership provides to [MyRole] members, but only on [dbo] SCHEMA?

Thank you!

Answer 1

Some of the permissions you're trying to change don't exist in the way you're trying to use them.

For example, to deny CREATE TABLE and most other DDL statements, for a particular schema, the correct syntax and permission would be DENY ALTER ON SCHEMA::dbo TO MyRole.

This should cover the equivalent of the db_ddladmin role's permissions. (I know it's a little less than intuitive, but denying all of ALTER, like in my example above, does include denying the CREATE permissions as well.)