Creating a custom server role in SQL 2005

I'm a sys admin of a web farm, and currently my developers can connect to the SQL server as sysadmins.

Now, i want them to be able to connect to all DB's but only have perm开发者_如何学编程ission to modify the DB Schema and such, NOT create new DB's or change permissions.

is there anyway i can create a custom Server Role and configure what permissions it have?

---

I would put them in the following Database Roles as I don't think there is a Server Role that fulfils your requirements.

db_datareader
db_datawriter
db_ddladmin

Then remove them from the sysadmin Server Role.

I think you can then issue DENY statements to prevent them from using the CREATE,ALTER,DROP DATABASE statements.

e.g.

DENY CREATE DATABASE to UserXYZ

I haven't tested this so I'm not 100% sure if this is possible.

If you don't give them db_ddladmin permission in the master database then they won't be able to create any new databases.