Re: Limiting access to SQL Server 2000 for our junior developers

From: Dandy Weyn [Dandyman] (dandy_at_dandyman.net)
Date: 08/09/05 

Date: Tue, 9 Aug 2005 08:30:45 +0200

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:
>> 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...
>> > 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
>> >
>

Relevant Pages

  • Re: code access security
    ... Error 1 CREATE ASSEMBLY for assembly 'GmsSqlClr' failed because assembly ... owner has EXTERNAL ACCESS ASSEMBLY permission and the database has the ... make sure the database owner is mapped to the correct login on ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: low permission cannot convert from A97 to A2000/2003
    ... Delete is a separate permission. ... You need open exclusive permission on the database to ... > The admin user has full privileges. ... > as we wont be importing those files from them, and the contractor will be ...
    (microsoft.public.access.conversion)
  • Re: low permission cannot convert from A97 to A2000/2003
    ... Delete is a separate permission. ... You need open exclusive permission on the database to ... > The admin user has full privileges. ... > as we wont be importing those files from them, and the contractor will be ...
    (microsoft.public.access.security)
  • Re: Windows Power User SQL
    ... The guest user must have connect permission in master and tempdb. ... When I run from the master database for example testing against user bill ...
    (microsoft.public.sqlserver.security)
  • Could not Lock File error
    ... >I'm trying to share an access database with an entire ... >modifying this file with FULL PERMISSION on the parent ... >or not AND ONLY allowing one person to modify it's ... It's called an .LDB file, ...
    (microsoft.public.access.security)