Re: BuiltinAdministrator's not SysAdmin yet appear to have DBO on

From: DBADave (DBADave_at_discussions.microsoft.com)
Date: 01/25/05 

Date: Tue, 25 Jan 2005 11:51:02 -0800

db_ownerI am familiar with how permissions flow within SQL. I am seeing
something strange when reviewing permissions on DBs in EM and just want to
see if what I am seeing is valid or is somehow a bug.

Below is what I see:

The BUILTIN\Administrators (Local Admins) group is not a sysadmin (does have
ProcessAdmin & DiskAdmin permissions). The BUILTIN\Administrators group has
not been set as DBO to any DB and has not been made a member of the db_owner
role in each DB.

However, when I view the database permissions for the BUILTIN\Administrators
role, I see that it is listed as a member of the db_owner role for each of
the databases on the SQL server

So, in effect, the BUILTIN\Administrators group did not create any databases
on the server, doesn't have the permissions to do so and hasn't been set as
DBO or as a member of db_owner, yet db_owner is enabled/selected for each DB
on the server for the local admins group.

Has anyone seen/enocuntered this before in EM?

"Sue Hoegemeier" wrote:

> No...that's not what would typically happen if you remove
> the Builtin\administrators group.
> I'm guessing that you mean that the local admins appear to
> have db_owner role permissions DBO and db_owner are
> different things. DBO is a user and db_owner is a database
> role. Users can be members of db_owner fixed database role
> which gives them all permissions in the database.
> I'm not sure why you think the local admins on the server
> have db_owner permissions but one thing to keep in mind is
> that permissions are cumulative based upon the users
> explicit permissions as well as those inherited from group
> membership (Windows groups as well as the server and
> database groups).
>
> -Sue
>
> On Sun, 23 Jan 2005 06:33:03 -0800, "DBADave"
> <DBADave@discussions.microsoft.com> wrote:
>
> >Hi All,
> >
> >I have revoked the BUILTIN\Administrators group membership in the SysAdmin
> >group of a SQL 2000 server and instead granted the group Process
> >Admoinistrators and Disk Administrators permissions. When I browse
> >permissions for the BUILTIN\Administrators group in EM, I see the proper
> >Server Roles are defined as noted above, however this group appears to have
> >DBO permissions to all databases on the server, even though those databases
> >were created by SA. In checking several of my SQL servers I am also seeing
> >the same thing on each server. Have people seen this before? This would
> >imply that the Local Admins to the box stil have dbo to all of the databases,
> >but they were never granted this permission. Is this just in incorrect
> >representation within EM?
> >
> >Thanks,
> >Dave
>
>