Hi,

I need to create couple of database roles to restrict the developers working on our development database (Sqlserver 2005).

Need roles similar to the one listed below

1.Web User -

# Has access to execute only Stored Procedures & UDFs

(Created a db_Executor role to solve this scenario. works fine)

2.Junior Developer - Access only to read data, create and execute procedures

Created a role and made the role inherit the privileges of db_executor and db_datareader role to allow data read and execute procedures.

How do i give access to create stored procedures only ?

3.Mid Developer -

#Access to read,write data

#Alter schema but cannot drop any objects (tables,views ect..)  - How do i do this ?

#Create and modify procedures - How do I acheive this?

4.Senior Developer

# Read and write data

# Execute and write procedures

# Alter schema

One way would be to use a combination of the 'IS_MEMBER' T-SQL function along with DDL triggers.

For instance you could define a DDL trigger on the database that fires on the DROP_PROCEDURE event and checks to see if the user is a member of a particular database role then either rolls back the transaction or does nothing, as appropriate.

IS_MEMBER(): http://msdn.microsoft.com/en-us/library/ms186271.aspx
DDL Trigger events: http://msdn.microsoft.com/en-us/library/ms189871.aspx

Alternatively you could examine the following list of available database permissions to see if any of those available suit your requirements:

http://msdn.microsoft.com/en-us/library/ms178569.aspx

Chris