3

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.

2

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.

  • 1
    But giving the ddladmin role will also give rights to the database objects such as tables right, but the right to alter tables should be revoked. – jason Feb 11 '16 at 17:34
  • 1
    Correct. ddladmin gives the ability to modify the structure (but not the data) of a table. If you don't want to give someone that option I would recommend using the second option. Grant CREATE PROCEDURE, CREATE VIEW and ALTER SCHEMA on dbo (assuming that's the schema you want them to have access too). – Kenneth Fisher Feb 11 '16 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.