Re: Visual Studio gives direct dbo access to Sql Server 2000 ??? <<Update>>

From: Dan Artuso (dartuso_at_NSpagepearls.com)
Date: 07/19/05 

Date: Tue, 19 Jul 2005 09:48:20 -0400

Hi,
If you check out the logins in SQL Server, you'll see the BuiltIn\Administrators login.
This login is a part of the sysadmin server role.
What this means is that any account that is a member of the machine's local admin group
will also be a sysadmin in SQL Server.
If the developers need local admin rights, you can remove the BuiltIn\Administrators from the sysadmin role.
If you do that, make sure you make the appropriate accounts sysadmins so you don't lock yourself out.

What are developers doing with domain admin rights anyway?? 

-- 
HTH
Dan Artuso, Access MVP
"BBFrost" <barry.b.frost@remove_this.wrd.state.or.us> wrote in message news:exA9nH$iFHA.2152@TK2MSFTNGP14.phx.gbl...
> New Info ...
>
> A couple of the developers turned out to be NT Domain administrators.  We
> removed the developers from the NT Domain Admin list and they now have to
> login (over and over again) to access their tables.  They're not very happy.
> So far it seems that being an NT Domain Administrator allows one to "Blow
> Right past" Sql Server's Security checks and access the server with "dbo"
> rights.
>
> Can anyone elaborate on what's going on here ???   Is there a way to allow
> the developers to be NT Domain Admins without automatically granting them
> "cart blanc" DB access ??
>
> Again ... Thanks in advance.
>
> Barry
> in Oregon
>
> "BBFrost" <barry.b.frost@remove_this.wrd.state.or.us> wrote in message
> news:ew9p9j#iFHA.2472@TK2MSFTNGP15.phx.gbl...
> > Greetings,
> >
> > I'm a new Sql Server administrator and I just received a shock ...  We've
> > set up a pretty much default instance of Win2003 server and SqlServer
> 2000.
> >
> > Just told the developers that I'd set up a test Sql Server instance and
> told
> > them the name. Shortly there after I found that they were connecting from
> > Visual Studio are getting automatic 'dbo' access.
> >
> > Tables and views are popping up all over the place, all owned by dbo and
> I'm
> > having a tough time figuring out what's going on.
> >
> > I've set the Sql Server 2000 Security parameters to "Sql Security &
> Windows"
> > and I've created individual user accounts for our test database.  (The
> > security options I see are "SQL Server and Windows" & "Windows Only".
> > Selecting "SQL Server and Windows" doesn't seem to limit the developers
> from
> > charging in with "Window Only" access.
> >
> > For example "smithca" has a Windows NT Domain account and Visual Studio.
> >
> > Within the test_db_server database I've created a "smithca" user account.
> >
> > With the test_db "smithca" has been granted the "public" roll.
> >
> > When "smithca" fires up Visual Studio Server Explorer, points at
> > "test_db_server" and creates a "Window NT integrated security" account.
> > WHAM! He's got "dbo" access to the database.
> >
> > From what I can see any developer can log into any database (test or
> > production) with full DBA permissions.
> >
> > Afraid ... very afraid !!!
> >
> > Any help with this would be greatly appreciated !!!
> >
> > Thanks in advance
> >
> > Barry
> > in Oregon
> >
> >
> >
>
>

Relevant Pages

  • Re: SQL Login
    ... The sa login account is a member of that role as well. ... Dejan Sarka, SQL Server MVP ... > But how do we add the User X to the sysadmin / ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2005 Express setup
    ... Because SQL Server would create a Login for Local Administrators and add this Login to the System Administrator "sysadmin" role. ... However, if you want your Login to be a System Administrator, then you should log in to your SQL Server Instance as a System Administrator and add your own Login to the sysadmin Fixed Server Role. ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL 2005 Express setup
    ... Connect to your SQL Server Instance, ... Double click on the Login that you want to add to the "sysadmin" server ... Mark the "sysadmin" checkbox to make this Login a member of this Server ...
    (microsoft.public.sqlserver.setup)
  • Re: How to create views with SQL-NS
    ... >> What programming language are you using for your Wizard? ... all of our developers have EM and QA. ... >>>creation within our security model that sits atop SQL Server. ...
    (microsoft.public.dotnet.framework.interop)
  • Re: MSDE password change for "sa"
    ... I login to the machine as a local admin and connect via the ... As a sysadmin, you can execute the following to ... >> Upgrading to SQL Server isn't too bad. ... You will be asked if you want to upgrade ...
    (microsoft.public.sqlserver.server)