Re: Restrict access to single db from members of server administrator role

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 12/07/04 

Date: Tue, 7 Dec 2004 07:34:42 -0600

Sysadmin role members have full permissions and you can't deny/revoke
permissions from this role.

It's a good practice to use separate instances for development and
production. It seems to me that your support database belongs on the 'live'
server rather than the development one. This would allow you to provide
developers full access to the development instance while protecting your
database as you see fit. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Keith Langmead" <klangmead@nospam.kms.co.uk> wrote in message 
news:eoBQcAE3EHA.3092@TK2MSFTNGP10.phx.gbl...
> 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

  • Permissions for database
    ... This is a common problem. ... For each database, create a developer role and a Readonly ... The developers can be ... should have select permissions and execute permissions. ...
    (microsoft.public.sqlserver.security)
  • Restrict access to single db from members of server administrator role
    ... 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 ... If I added the group to the Database ...
    (microsoft.public.sqlserver.security)
  • Modifications to sshd and sftp-server: new functionality
    ... I run a web development server which offers ... secure ftp for our web content developers. ... wanted to be able to log ftp transactions on a per-user basis (file removal, ... permissions are hard-coded into the server. ...
    (comp.security.ssh)
  • 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: dbo role and modify filesize of database
    ... If your developers need permission to create objects owned by other users, ... permissions on them but will not be able to alter the database. ... Another technique is to just grant them CREATE permissions for the ... >> permissions and control access via role membership. ...
    (microsoft.public.sqlserver.security)