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?
Best Answer
Quick answer: For developers, you can GRANT CONTROL on that schema.
Background:
GRANT
ed: GRANT Schema PermissionsThe intersection of these two give the schema permission meanings:
CONTROL
implies the rest and is the highest permissions of any securableSELECT, DELETE, INSERT, UPDATE
is DML on objects in that schemaEXECUTE
to run scalar UDFs and Stored Procedures in that schemaALTER, REFERENCES
is DDL (CREATE, ALTER, DROP) on objects in that schemaVIEW DEFINITION
lets folk see the code and definitionsExcept for CONTROL, these are mostly orthogonal to each other: folk can
EXECUTE
code orSELECT
, 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