Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have several databases under development and I would like to start using SQL Server schemas for permission management and logical grouping of database objects. Under Schema Properties there is explicit permissions like

Alter
Control
Delete
Execute
...

What is the hierarchy of these permissions? The idea is to grant Control for admin AD group, and give developer AD group some rights that allow them to do their work (ddl, read, write). What rights should be given for developers?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

Quick answer: For developers, you can GRANT CONTROL on that schema.

Background:

The intersection of these two give the schema permission meanings:

  • CONTROL implies the rest and is the highest permissions of any securable
  • SELECT, DELETE, INSERT, UPDATE is DML on objects in that schema
  • EXECUTE to run scalar UDFs and Stored Procedures in that schema
  • ALTER, REFERENCES is DDL (CREATE, ALTER, DROP) on objects in that schema
  • VIEW DEFINITION lets folk see the code and definitions

Except for CONTROL, these are mostly orthogonal to each other: folk can EXECUTE code or SELECT, from a view but not see the definition (VIEW DEFINITION) of these.

You also have the database and server permissions which are implied higher up then CONTROL on schemas: see Permissions Hierarchy

share|improve this answer
    
Thanks, if I give CONTROL for developers, can they make changes for permission e.g of this schema? –  jrara Oct 18 '11 at 8:14
    
@jrara: Only if you use "WITH GRANT OPTION" I think. I normally allow developer db_owner in their own databases so haven't tried it. I can't do any tests either just now. –  gbn Oct 18 '11 at 8:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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