Re: Set up permissions for developers

Dave Vick (dsvick@xxxxxxxxx) writes:
We have a couple of different levels of people that have need to
access our SQL Server 2000 instance. What I would like to do is set up
a Developer role and give everyone in that role read - write access to
a specific set of databases on the server. In addition they would need
to be able to create new DBs. I see that I can add a new role with
sp_AddRole, but from what I can see and have read it has to be done
once for each database. I'd like to create the role, set the
permissions on it and just add the users to it. Or is there a better
way to do this?

Maybe. Maybe not. Probably not.

Normally you add database permissions per database. You can create a role,
add users to that role and add that role to db_datawriter and db_datareader.

The only thing you can do on server level is do this work in the model
database. Then all new databases on the server will get these users,
roles and permissions. Which is maybe not what you want. And top on of
that users would be able to change model.

A better alternative is to package the whole thing in a script that the
users can run in their newly created database.

One thing you can do server level: you can add them to the dbcreator fixed
server role to permit them to create databases.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages