Restricting Access to BUILTIN\Administrators

From: BC DBA (BCDBA_at_discussions.microsoft.com)
Date: 06/22/05 

Date: Wed, 22 Jun 2005 04:36:05 -0700

I have a bunch of SQL servers (2000, and 7) that I inherited when I took over
as the DBA in my organisation. Due to decisions outside my control there are
a number of users that have been granted Domain Admin rights which
automatically grants them sa privileges to the SQL Servers via
BUILTIN\Administrators.

Legislation requires us to restrict access to data to those individuals that
require access, so I need to prevent members of the Domain Administrators
group access to the servers.

What I thought I could do was to create another Domain Group say SQL Server
Administrators. Grant that the System Administrator role to the NT Group and
then Deny Login to the BUILTIN group. See the problem? If you are a member of
both accounts then you are denied access (Deny supercedes Grant).

Next thought remove the BUILTIN group from the System Administrators Role
and remove access to each of the databases on the server. Problem is that it
has database owner ticked for each database in EM and when I remove that I
get the following

Error 15405: Cannot use the reserved user or role name 'dbo'.

Looking at one of the databases my domain user is the owner and there is no
other user so I don't think that changing the dbo for each of the databases
will help. Anyone any other ideas (I have thought about removing the users
from the Domain Admins group but I would upset a lot of people) 

-- 
Regards
Tony

Relevant Pages

  • Re: Planning to move SQL 7.0 to 2000 on to a new server
    ... http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases ... Issues When a Database Is Moved Between SQL Servers ... What databases to I> need to do this other than user databases? ...
    (microsoft.public.sqlserver.security)
  • Migrating DTS to several locations
    ... I have over 182 DTS packages that reside on 54 SQL servers each is a diffrent ... server, the DTS packages pull data from SQL server databases, sybase & ... The plan is to move the databases to 4 SQL servers, ...
    (microsoft.public.sqlserver.dts)
  • Help on code in a SP
    ... I'm currently auditing some SQL servers to have an overview on what's ... running/existing in the different databases. ... I found User's Stored Procedures in MSDB databases ...
    (microsoft.public.sqlserver.server)
  • Re: Large DB vs Several Small DBs?
    ... I manage dbs in 2 enviroments. ... 100 - SQL Servers ... We are in the process of adding the ability of each customer having ... multiple databases. ...
    (microsoft.public.sqlserver.server)