I recently have this request from a developer lead:

============================================================
we need another account that we could give to the Analysts so that they 
would not be able to change the table definition, keys, indexes, and 
triggers, but could still create stored procs and functions, etc. we need 
another account that we could give to the Analysts so that they would not be 
able to change the table definition, keys, indexes, and triggers, but could 
still create stored procs and functions, etc.
======================================================================================

Would I have to set up a new database role to accomplish this? What 
permissions would be necessary? 

Loren Z (anonymous@discussions.microsoft.com) writes:
> I recently have this request from a developer lead:
> 
>============================================================
> we need another account that we could give to the Analysts so that they 
> would not be able to change the table definition, keys, indexes, and 
> triggers, but could still create stored procs and functions, etc. we
> need another account that we could give to the Analysts so that they
> would not be able to change the table definition, keys, indexes, and
> triggers, but could still create stored procs and functions, etc. 
>===========================================================================
> 
> Would I have to set up a new database role to accomplish this? What 
> permissions would be necessary? 
 
Yes, you should create a role for this, and add analysts as needed to
this role. You should grant this role CREATE PROCEDURE and CREATE FUNCTION,
beside EXEC, INSERT, SELECT, DELETE and UPDATE.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I tried creating the role but I cannot see where it can be granted CREATE 
PROCEDURE and CREATE FUNCTION permissions.


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9CE6A25B8699EYazorman@127.0.0.1...
> Loren Z (anonymous@discussions.microsoft.com) writes:
>> I recently have this request from a developer lead:
>>
>>============================================================
>> we need another account that we could give to the Analysts so that they
>> would not be able to change the table definition, keys, indexes, and
>> triggers, but could still create stored procs and functions, etc. we
>> need another account that we could give to the Analysts so that they
>> would not be able to change the table definition, keys, indexes, and
>> triggers, but could still create stored procs and functions, etc.
>>===========================================================================
>>
>> Would I have to set up a new database role to accomplish this? What
>> permissions would be necessary?
>
> Yes, you should create a role for this, and add analysts as needed to
> this role. You should grant this role CREATE PROCEDURE and CREATE 
> FUNCTION,
> beside EXEC, INSERT, SELECT, DELETE and UPDATE.
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 

Loren Z (anonymous@discussions.microsoft.com) writes:
> I tried creating the role but I cannot see where it can be granted CREATE 
> PROCEDURE and CREATE FUNCTION permissions.

Don't you have a query window?

GRANT CREATE PROCEDURE TO newrole
GRANT CREATE FUNCTION TO newrole

It's as simple as that.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx