Re: Set up permissions for developers

Be careful with the dbcreator role. It allows its members to create and drop any database. If you don't want the developers to drop others' databases, you will have to create a specific permission to do so. In sql 2000, I think it is "CREATE DATABASE" Also, since there are no non-fixed server roles, you would have to use a windows group, and windows authentication to apply a permission like that to a set of changing users.


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message news:Xns99AFDDE21E8Yazorman@xxxxxxxxxxxx
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

  • ADP, Application Role, and objects
    ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
    (microsoft.public.access.adp.sqlserver)
  • RE: WSS install locked into MSDE DB - Can not install for SQL Serv
    ... For the MASTER database ... Althought we change the Configuration Application pool several times ... interface BUT they appear to be MSDE databases and not SQL Server databases. ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: How to Replicate an SQL Server 2000 Database
    ... actual server name) enterprise manager should associate the database with the ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files
    ... I have had quite a number of clients that used a SAN for a database server and suffered significant performance problems because their underlying SAN infrastructure was a huge stripe across many drives. ... By application files I mean the binn folder which contains the sql server executable among other things. ...
    (microsoft.public.sqlserver.setup)
  • Re: FCB::RemoveAlternateStreams: Operating system error (null)
    ... Thanks for all your help Ekrem, even if your sugestion does not solve the ... in this edition of SQL Server the ... database so it's opened again and you get this error somehow. ...
    (microsoft.public.sqlserver.setup)