Re: Security - give developers read access to prod DBs

From: Rick Sawtell (quickening_at_msn.com)
Date: 12/02/04 

Date: Thu, 2 Dec 2004 11:46:50 -0600

"Danny Falkirk Council" <Danny Falkirk Council@discussions.microsoft.com>
wrote in message news:4096DAB1-E2B2-4983-B756-8E94522EDC7F@microsoft.com...
> We have a test SQL 2000 server where most developers have their own SQL
> instance, which they are encouraged to administer as a means of knowledge
> sharing. Changes to the prod SQL 2000 server are channeled through myself
> as
> SQL DB administrator. I want to allow all developers read access to all
> databases on the prod SQL instance where all development databases are
> stored
> but, if possible, want to avoid back-door ways of them making changes. I
> also want it as automated as possible.
>
> Is there a recommended approach or one that is in use and comes
> recommended?
> Am I trying to achieve the impossible? We thought that using the Public
> role would give us a solution, but this is flawed as Public can
> legitimately
> be granted greater access to objects. We thought of setting up a
> Developer
> role to ensure read access as a minimum, but couldn't see how to automate
> this and also assumed Public access could still give back-door entry.

Create a Windows Group called Developer.
Assign devs to that group.
Add group as a login to the production server.
Grant access to group to each database.
Lock down the group to denydatawriter in each db.

Ensure that your devs don't have any other type of admin permissions in the
database through their Windows authenticated account.

HTH

Rick Sawtell
MCT, MCSD, MCDBA

Relevant Pages

  • Re: Side-by-side upgrade - moving master, msdb and model
    ... MVP - Windows Server - Clustering ... "In some cases, you may want to copy the system databases, including ... from the source SQL Server 2000 instance to the SQL ...
    (microsoft.public.sqlserver.clustering)
  • Re: BizTalk Server 2004 ConfigFramework.exe error
    ... one BizTalk is installed on? ... machines (both for SQL and DTC) is not blocked by firewalls and new MSDTC ... Multi-computer configuration using Windows Server 2003 or Windows XP ... >>all associated databases. ...
    (microsoft.public.biztalk.general)
  • RE: Clustering a standalone SQL server + storage array
    ... working as expected with out error your ready to install sql server. ... log files to the data and log shared drives you setup as part of the cluster. ... Our apps team look after a trading system that uses an HP DL360 server ... running SQL 2000 SP3a with a few small databases on it. ...
    (microsoft.public.sqlserver.clustering)
  • RE: Multiple Access and ODBC connections
    ... We're running Access 2003, SQL 2005. ... server when we deploy a new release. ... We are linked to test SQL and Access DBs, and developers change the links to ...
    (microsoft.public.access.modulesdaovba)
  • Re: Side-by-side upgrade - moving master, msdb and model
    ... MVP - Windows Server - Clustering ... "In some cases, you may want to copy the system databases, including the ... from the source SQL Server 2000 instance to the SQL ...
    (microsoft.public.sqlserver.clustering)