Remove/Add BUILTIN\Administrators 2000 sp4

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DBA449 - 05 Jul 2006 21:05 GMT

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..."

Reply to this Message

Uri Dimant - 06 Jul 2006 06:12 GMT

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

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

> When I delete the BUILTIN\Administrators login and then re-add it. I get
> the
[quoted text clipped - 17 lines]
> solution. But all of them with a similiar problem end with, "try
> sp_changedbowner..."

Reply to this Message

DBA449 - 07 Jul 2006 19:57 GMT

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.

> DBA449
> > How does it know that? Where is this information stored?
[quoted text clipped - 28 lines]
> > solution. But all of them with a similiar problem end with, "try
> > sp_changedbowner..."

Reply to this Message

Uri Dimant - 09 Jul 2006 08:39 GMT

> 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.

> Thanks for you reply, but..
> That is not precisely correct. That is acutally a Windows Group, not a
[quoted text clipped - 53 lines]
>> > solution. But all of them with a similiar problem end with, "try
>> > sp_changedbowner..."

Reply to this Message

DBA449 - 10 Jul 2006 20:05 GMT

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.

> > That is not precisely correct. That is acutally a Windows Group, not a
> > login. A
[quoted text clipped - 66 lines]
> >> > solution. But all of them with a similiar problem end with, "try
> >> > sp_changedbowner..."

Reply to this Message

Sue Hoegemeier - 10 Jul 2006 21:42 GMT

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

>Thanks for working with me on this one.
>I have seen a windows group referred to as a LOGIN for sql server in the
[quoted text clipped - 75 lines]
>> >> > solution. But all of them with a similiar problem end with, "try
>> >> > sp_changedbowner..."

Reply to this Message

DBA449 - 19 Jul 2006 20:00 GMT

Thanks Sue for your response.
I check all of the databases, BUILTIN\Administrators is not in sysusers of
any of the databases. However these databases were likely crated by an
account that was a member of the Administrators group. That account does not
exist anywhere in sql server either.
Is there anywhere else where this information might be stored that would
cause the message seen below.

> As to your original question, the message of "it has been
> detected that this login has permissions....etc this login
[quoted text clipped - 109 lines]
> >> >> > solution. But all of them with a similiar problem end with, "try
> >> >> > sp_changedbowner..."

Reply to this Message

Sue Hoegemeier - 27 Jul 2006 17:21 GMT

Check the sysusers sids and compare it with the syslogins
sids to check for sids in the database that don't have
logins.
The users, logins are just in system tables already
mentioned. The creator sid is in sysdatabases.

-Sue

>Thanks Sue for your response.
>I check all of the databases, BUILTIN\Administrators is not in sysusers of
[quoted text clipped - 117 lines]
>> >> >> > solution. But all of them with a similiar problem end with, "try
>> >> >> > sp_changedbowner..."

Reply to this Message

DBA449 - 27 Jul 2006 20:09 GMT

Only the guest account showed up as being in the database but not in
syslogins. A query of sysdatabases shows a sid value of 0x01 for all
databases.

select * from prop_0000.dbo.sysusers su
where su.sid not in (select sid from master.dbo.syslogins)

I welcome other ideas cause I have run out of them my self...

> Check the sysusers sids and compare it with the syslogins
> sids to check for sids in the database that don't have
[quoted text clipped - 125 lines]
> >> >> >> > solution. But all of them with a similiar problem end with, "try
> >> >> >> > sp_changedbowner..."

Reply to this Message