Limiting access to SQL Server 2000 for our junior developers

Hi All
I've been given a task to lockdown access to our database server to our
6 junior developers. We currently have about 25 databases on our SQL
Server. I'm trying to do the following the easiest way:
1. Create six separate logins
2. For all 25 db's only allow selects, inserts and updates for them to
all tables.
3. They must be able to create tables but not drop or truncate
anything.
4. Of course no access to the master db
5. Lock out all dangerous sp's like xp_cmdshell
I'm new to SQL permissions. What would be the easiest way to doing
this without manually adding all the permissions table level for each
login?
Thanks in advance for any help.
BillCreate a database role
grant the role data_reader permission
grant the role data_writer permission
grant the role the CREATE TABLE permission
deny the role DELETE Permission on any object
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
<bcrenshaw99@.yahoo.com> wrote in message
news:1123541934.751731.34430@.o13g2000cwo.googlegroups.com...
> Hi All
> I've been given a task to lockdown access to our database server to our
> 6 junior developers. We currently have about 25 databases on our SQL
> Server. I'm trying to do the following the easiest way:
> 1. Create six separate logins
> 2. For all 25 db's only allow selects, inserts and updates for them to
> all tables.
> 3. They must be able to create tables but not drop or truncate
> anything.
> 4. Of course no access to the master db
> 5. Lock out all dangerous sp's like xp_cmdshell
> I'm new to SQL permissions. What would be the easiest way to doing
> this without manually adding all the permissions table level for each
> login?
> Thanks in advance for any help.
> Bill
>|||Dandy
> grant the role data_writer permission
If you add him to the db_denydatawriter hi cannot be able to delete
anything
Also , don't forget to execute EXEC sp_defaultdb 'LoginName', 'database'
as well as EXEC sp_grantdbaccess 'test'
"Dandy Weyn [Dandyman]" <dandy@.dandyman.net> wrote in message
news:eAwwwNKnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Create a database role
> grant the role data_reader permission
> grant the role data_writer permission
> grant the role the CREATE TABLE permission
> deny the role DELETE Permission on any object
>
> --
> Dandy Weyn
> [MCSE-MCSA-MCDBA-MCDST-MCT]
> http://www.dandyman.net
> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
> <bcrenshaw99@.yahoo.com> wrote in message
> news:1123541934.751731.34430@.o13g2000cwo.googlegroups.com...
>|||Hey Dandy
Thanks for the quick response :-)
I would assume that because database roles are created per database, I
would have do this on all 25 db's?
Dandy Weyn [Dandyman] wrote:[vbcol=seagreen]
> Create a database role
> grant the role data_reader permission
> grant the role data_writer permission
> grant the role the CREATE TABLE permission
> deny the role DELETE Permission on any object
>
> --
> Dandy Weyn
> [MCSE-MCSA-MCDBA-MCDST-MCT]
> http://www.dandyman.net
> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
> <bcrenshaw99@.yahoo.com> wrote in message
> news:1123541934.751731.34430@.o13g2000cwo.googlegroups.com...|||Exactly,
However if you would add the role to the deny_data_writer as Uri specified,
the user would also not be able to insert/update which was needed in your
case.
You need to do this on every database yes.
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
<bcrenshaw99@.yahoo.com> wrote in message
news:1123565986.701157.304890@.g49g2000cwa.googlegroups.com...
> Hey Dandy
> Thanks for the quick response :-)
> I would assume that because database roles are created per database, I
> would have do this on all 25 db's?
>
>
> Dandy Weyn [Dandyman] wrote:
>|||Right, my mistake
"Dandy Weyn [Dandyman]" <dandy@.dandyman.net> wrote in message
news:OUxktvKnFHA.1468@.TK2MSFTNGP12.phx.gbl...
> Exactly,
> However if you would add the role to the deny_data_writer as Uri
> specified, the user would also not be able to insert/update which was
> needed in your case.
> You need to do this on every database yes.
>
> --
> Dandy Weyn
> [MCSE-MCSA-MCDBA-MCDST-MCT]
> http://www.dandyman.net
> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
> <bcrenshaw99@.yahoo.com> wrote in message
> news:1123565986.701157.304890@.g49g2000cwa.googlegroups.com...
>