Re: Remove/Add BUILTIN\Administrators 2000 sp4

As to your original question, the message of "it has been
detected that this login has permissions....etc this login
will have access to these databases now" is really from
adding a login that already had a user account for a
database. If I am a member of the local admins on a SQL
Server where the BUILTIN\Administrators account is left at
the default, I will have sysadmin access and my access is
via group membership (the windows local admins group). If I
create a database and leave me as the owner, my login will
be mapped to dbo. If I then explicitly add my windows
account to the SQL logins, I would get the message you
posted and my newly added login would be given access to the
database. It knew that because my login was already mapped
to dbo and the sid for my login was already present in the
sysusers system table for that database. That's where that
messages comes from.
I'd suspect that more of your issues are from changes to how
the default permissions, access were set for the
Builtin\Administrators group. You don't want to mess with
the system tables but I would certainly take a look at
sysusers in the user databases and syslogins in master. It
may help if you query sysusers using something like:
select suser_sname(sid) as LoginName, *
from sysusers
just so you have the logins, what login is actually mapped
to dbo, etc.

If you are convinced you have hit a bug, you can open a
support case with Microsoft support. You aren't charged if
it's a bug.

-Sue

On Mon, 10 Jul 2006 12:05:01 -0700, DBA449
<DBA449@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Thanks for working with me on this one.
I have seen a windows group referred to as a LOGIN for sql server in the
documentation, so yes I would agree.

On an identical test system, I was able to uncheck the System Administrators
Server Role for BUILTIN\Administrators. But on the production system, I can
not for the reasons sited below. And the database owner in all cases is "sa".
This simply must be a bug in SQL Server.

"Uri Dimant" wrote:

That is not precisely correct. That is acutally a Windows Group, not a
login. A


Could not a win group be a LOGIN to SQL Server?

Server, it some how knows that on this server, it use to be the dbo in all
the databases, and so it makes it dbo on all databases again.

By default , this Login is a member of sysadmin server role and db_owner
database fixed role and mapped to DBO user at all databasees
DBO is just a "privileged" user has full permissions to perform all
activities in the database.





"DBA449" <DBA449@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F440ABBC-3636-48A0-BDDF-7CE482A2E4C3@xxxxxxxxxxxxxxxx
Thanks for you reply, but..
That is not precisely correct. That is acutally a Windows Group, not a
login. And they have permission by default only because this windows group
is
by default a member of the SQL Server Role "System Administrator".

The original question was, when I delete and recreate this group in SQL
Server, it some how knows that on this server, it use to be the dbo in all
the databases, and so it makes it dbo on all databases again. Something I
am
trying to prevent.. On other servers, it is not dbo and
BUILTIN\Administrators has no access to anything, which is my goal.
I already did a sp_changedbowner in all the databases to sa and that
succeeded. Yet I can not remove BUILTIN\Administators as dbo to the
databases, even when I delete and recreate BUILTIN\Administrators.

Any other ideas would be appreciated.


"Uri Dimant" wrote:

DBA449
How does it know that? Where is this information stored?

select loginname from master..syslogins

By default all members of Administrators Group have an access to SQL
Server
via this LOGIN


ttp://vyaskn.tripod.com/sql_server_security_best_practices.htm --------security
best practices





























"DBA449" <DBA449@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3DD8C090-FD3B-48B8-A4E5-9D6BCE4CCED5@xxxxxxxxxxxxxxxx
When I delete the BUILTIN\Administrators login and then re-add it. I
get
the
message from Enterprise Manager. "It has been detected that this login
has
permissions in specific database(s) - the login will have access to
these
databases now. "

How does it know that? Where is this information stored?

My motivation for doing this is that I need to remove access of
BUILTIN\Administrators from all user databases. It is currently mapped
to
dbo
in all the user databases. When I attempt to remove BUILTIN\
Administrators
from the dbo role, I get "Can not use the reserved word user or role
name
'dbo'.

I already succeeded in changing the dbo for all user databases using
sp_changedbowner to sa. I've also have already searched all new groups
for
a
solution. But all of them with a similiar problem end with, "try
sp_changedbowner..."







.

Relevant Pages

  • Re: Remove/Add BUILTINAdministrators 2000 sp4
    ... I have seen a windows group referred to as a LOGIN for sql server in the ... the databases, and so it makes it dbo on all databases again. ...
    (microsoft.public.sqlserver.security)
  • Re: Remove/Add BUILTINAdministrators 2000 sp4
    ... any of the databases. ... exist anywhere in sql server either. ... detected that this login has permissions....etc this login ... be mapped to dbo. ...
    (microsoft.public.sqlserver.security)
  • Re: Remove/Add BUILTINAdministrators 2000 sp4
    ... I check all of the databases, BUILTIN\Administrators is not in sysusers of ... exist anywhere in sql server either. ... detected that this login has permissions....etc this login ... be mapped to dbo. ...
    (microsoft.public.sqlserver.security)
  • Logins
    ... We have a SQL Server login which is used by two applications, ... separate databases on the server. ... information for the databases for which the login has been added as a user. ...
    (microsoft.public.sqlserver.security)
  • Re: Remove/Add BUILTINAdministrators 2000 sp4
    ... Could not a win group be a LOGIN to SQL Server? ... the databases, and so it makes it dbo on all databases again. ...
    (microsoft.public.sqlserver.security)