A while back I talked about the fact that you can grant someone CREATE PROCEDURE and ALTER on a schema in order to let them create a stored procedure in that schema. Rather than adding them to the db_ddladmin role and granting them the ability to create all kinds of objects in any schema in the database, among other things.
GRANT CREATE PROCEDURE TO [Ken.Fisqher];
GRANT ALTER ON SCHEMA::[dbo] TO [Ken.Fisqher];
What if I want the user to be able to create VIEWS, TABLES, FUNCTIONS, etc. Basically, I want a db_ddladmin Jr. Something I can give to a developer that is focused on a single schema. Say the reporting schema.
The best practice is, of course, to use a role. Which against expectations I’m going to call db_ddlcreator. Ok, who expected me to call it db_ddladmin_jr? I’ll admit, I did think about it but I decided the names might get a bit confusing. Not that db_ddlcreator is that great a name. Look. I’m bad at naming things. You name it however you want.
So what permissions am I going to grant it? Let’s start with this piece of Microsoft’s SQL Server Permissions poster:
First thing, I’m going to ignore all of the ALTER permissions. From what I’m seeing they are outside the scope of something I can give to my schema-focused developer. The CREATEs I’ll keep, of course, along with REFERENCES (which lets them create FOREIGN KEYS). I’m going to skip CHECKPOINT because it’s also outside of what I think they should have for this particular role.
CREATE ROLE [db_ddlcreator_SchemaName];
GRANT ALTER ON SCHEMA::[SchemaName] TO [db_ddlcreator_SchemaName];
GRANT CREATE AGGREGATE TO [db_ddlcreator_SchemaName];
GRANT CREATE DEFAULT TO [db_ddlcreator_SchemaName];
GRANT CREATE FUNCTION TO [db_ddlcreator_SchemaName];
GRANT CREATE PROCEDURE TO [db_ddlcreator_SchemaName];
GRANT CREATE QUEUE TO [db_ddlcreator_SchemaName];
GRANT CREATE RULE TO [db_ddlcreator_SchemaName];
GRANT CREATE SYNONYM TO [db_ddlcreator_SchemaName];
GRANT CREATE TABLE TO [db_ddlcreator_SchemaName];
GRANT CREATE TYPE TO [db_ddlcreator_SchemaName];
GRANT CREATE VIEW TO [db_ddlcreator_SchemaName];
GRANT CREATE XML SCHEMA COLLECTION TO [db_ddlcreator_SchemaName];
GRANT REFERENCES TO [db_ddlcreator_SchemaName];
There you go. Feel free to add/remove any permissions you think appropriate to your situation.