0

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!

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.)

6
  • 1
    Thanks, I am actually even more confused how they don't exist but are actually granted ... I was executing the DENY ALTER ON SCHEMA::dbo TO MyRole does prevent the user to alter a table (thanks!), but i tried and can still CREATE table. How can I DENY CREATE TABLE ON SCHEMA ... that gives the same error. – Avi Jan 13 at 20:07
  • Google took me directly here :- sqlservercentral.com/forums/topic/… which I think will help you – Stephen Morris - Mo64 Jan 13 at 21:23
  • @Avi Are you sure you tested properly?...Does your test user have other permissions / or is part of another group that could be granting them the CREATE TABLE permission? On it's own, DENY ALTER ON SCHEMA::dbo TO MyRole does prevent the CREATE TABLE permission as per it's documentation (CREATE TABLE is under the implicit permission for ALTER) and worked when I tested with a new User on my end. – J.D. Jan 14 at 1:42
  • @Avi You should verify your test User doesn't have other conflicting privileges using a script possibly like this StackOverflow answer. – J.D. Jan 14 at 1:44
  • 1
    @J.D. You are right, the test user cumulated other rights from the DDL_ADMIN membership. Now I ended up with custom roles, and just granting what is needed exactly, so i don't have to figure out all those nonsense rights from the list above. Thanks for the help, to pointing out that they are non existent in this form. – Avi Jan 14 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.