The question of How to grant the ability to create/alter stored procedures and views came up today on dba.stackexchange.com. It’s actually a question I’ve pondered in the past. There are two major methods to do this.

The Scalpel

If you’ve looked at the permissions available in SQL Server you’ll have noticed CREATE PROCEDURE, CREATE VIEW etc. so this seems like it has an easy answer. The reality is that it’s not quite as easy as it looks. It actually took me several years before I really got what was going on.

If you grant a user these permissions

GRANT CREATE VIEW TO [UserName];
GRANT CREATE PROCEDURE TO [UserName];

The user still won’t be able to create procedures or views. And if you think about it in the right way it makes sense. While the CREATE permissions give us the ability to create objects what they don’t give us is a place to put them. Schemas are containers for most of the objects in databases. So in order change the schema (by putting an object in it) we need to grant ALTER on it. So for the CREATE to work we need to:

GRANT CREATE VIEW TO [UserName];
GRANT CREATE PROCEDURE TO [UserName];
GRANT ALTER ON SCHEMA::[dbo] TO [UserName];

Now UserName will be able to create/alter/drop views and procedures within the dbo schema.

FYI it doesn’t really matter what order you run these in.

The Sledgehammer

Now another option is to add the user to the db_ddladmin role. Sometimes this is the right way to go. Other times it’s like using a sledgehammer to remove the wing from a fly. db_ddladmin grants the ability to create/alter/drop ANY object in the database in ANY schema. This is usually far more permission than you want to grant, but it’s still useful when that’s what you need. Otherwise only grant the permissions that are actually required.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication Tagged: microsoft sql server, security