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
-- 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];
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.