Restrict access to single db from members of server administrator role

From: Keith Langmead (klangmead_at_nospam.kms.co.uk)
Date: 12/07/04 

Date: Tue, 7 Dec 2004 09:34:31 -0000

On our local SQL 2000 server I've made all of our developers members of the
System Administrator role, so that they can create new db's, create and
alter permissions to them etc. without having to wait for me to do the
various things for them. (needless to say they don't get that level of
access to the live servers!).

Now in the support department we have a database we are creating to store a
load of the information we use regularly, which while not being top secret,
we would prefer it if no one outside the department was able to access the
information.

I had thought I could just deny the developers group from that db, but of
course that doesn't work, since I presume the server role takes precedence.

I then thought that I could remove the developers from the System
Administrators role, and instead give them SA access to all the databases
individually (with one obvious exception), but then they would be unable to
create new databases and sql users. If I added the group to the Database
Creators and Security Administrators roles they could make the required
changes to new databases, but would obviously also have access to the
database I'm trying to block.

One other solution would be to install another instance of SQL purely for
the support departments information, but considering how small the db's are
that I'm trying to protect, I think that would be major over kill.

Does anyone know of any way around this, without removing the ability of our
developers to do their job.

Thanks
Keith

Relevant Pages

  • Re: Need help on how to organize users and objects
    ... I could assign a custom database role to them though. ... In 2005, if a user creates an sp, which schema does it belong to? ... Security is a huge subject in SQL Server 2005, so I'd suggest you to spend ... the sysadmin; developers and viewers. ...
    (microsoft.public.sqlserver.security)
  • Re: Whats My Password?
    ... When using any instance of SQL Server you can access it via the network ... the database on my computer. ... My book is predominantly for Visual Studio/SQL Server ... developers as it discusses issues that all architectures are likely to see ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: But a fool with a tool is still a fool
    ... maintaining SQL code on the server (ex. ... suddenly inherit some great knowledge of database design and know how to ... When I 'broke from the norm', writing all the SQL code in the TQuery, using ... not in a .pas file and even the freshest of developers have been ...
    (borland.public.delphi.non-technical)
  • Re: Restrict access to single db from members of server administrator role
    ... Sysadmin role members have full permissions and you can't deny/revoke ... It seems to me that your support database belongs on the 'live' ... server rather than the development one. ... developers full access to the development instance while protecting your ...
    (microsoft.public.sqlserver.security)
  • Re: Datebase needed for tracking of drugs for a fire department!
    ... including for MS Access database development. ... There are good developers, don't get me wrong, but one has to ... Excel makes you think in math calculations. ... Shelf life of the drug could be loaded once and then access ...
    (microsoft.public.access.tablesdbdesign)
Quantcast