How can I create a role to create,update,delete stored procedures and views

what permissions do I need to give in order to achieve the above task to get users the capacity to create,update,delete stored procedures and views to a database role.

The easiest way is to add a user to the db_ddladmin role.

-- Older method
EXEC sp_addrolemember 'db_ddladmin','username';
-- New method
ALTER ROLE db_ddladmin ADD MEMBER username;

This will give the user the ability to create/modify/drop any object in any schema. Unfortunately that will include objects you probably don't want them to change (tables for example).

A more precise method is to use the CREATE permissions. There is a small trick to using them however. You will also need to grant ALTER on the schema where they can create/alter the objects. So to grant the ability to create procedures and views in the dbo schema you might do this.

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

Same with CREATE TABLE, CREATE FUNCTION etc. The reason you need both is this: CREATE PROCEDURE gives you the ability to create the procedure, ALTER schema gives you a place to put the procedure as you create it.

I should also point out that the CREATE permissions also grant the ability to alter, and drop.

StackExchange Database Administrators Q#129005, answer score: 3