thanks.

well to summarize. the new group that i gave system administration right to
does not show any db privs. it must be mapping to the dbo role behind the
scenes.

not all the dbs were created by a user in the builit/admin role. sql server
just seems to behave different when looking at the default builtinadmin group.

my main question was what rights i needed to give the new group of system
admins. i understand that i only need to give them the system admin role, i
do not need to also add rights to all the dbs explicitly

"Charles Wang[MSFT]" wrote:

> Hi Jason,
> In addition to Tibor's suggestion, I would like to add more comments for
> your first question here.
>
> I think that it is not redundant but necessary. You know that database
> level access is based on users but not logins, so there must be a user in
> each database mapping to a specific login, even a sysadmin login account
> without exception. Now let us first look at the definition about dbo in SQL
> Server Books Online:
> --------------------------------------------------------------
> The dbo is a user that has implied permissions to perform all activities in
> the database. Any member of the sysadmin fixed server role who uses a
> database is mapped to the special user inside each database called dbo.
> Also, any object created by any member of the sysadmin fixed server role
> belongs to dbo automatically.
>
> For example, if user Andrew is a member of the sysadmin fixed server role
> and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not
> as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed
> server role but is a member only of the db_owner fixed database role and
> creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The
> table belongs to Andrew because he did not qualify the table as dbo.T1.
>
> The dbo user cannot be deleted and is always present in every database.
>
> Only objects created by members of the sysadmin fixed server role (or by
> the dbo user) belong to dbo. Objects created by any other user who is not
> also a member of the sysadmin fixed server role (including members of the
> db_owner fixed database role):
> - Belong to the user creating the object, not dbo.
> - Are qualified with the name of the user who created the object.
> ---------------------------------------------------------------
>
> You said that your built-in admin account was given dbo on every database,
> so I believe that all of the databases were created by your built-in admin
> account. If one database was created by another login account with sysadmin
> fixed server role, the dbo should have been mapped to that login and in
> this case, your build-in admin account would map to another user, by
> default same as the login name, who is assigned with the db_owner database
> role.
>
> If you have any other questions or concerns, please feel free to let me
> know.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== =========
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@microsoft.com.
> ================================================== =========
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> ications.
>
> Note: The MSDN Managed Newsgroup support offering is for
> non-urgent issues where an initial response from the community
> or a Microsoft Support Engineer within 1 business day is acceptable.
> Please note that each follow up response may take approximately
> 2 business days as the support professional working with you may
> need further investigation to reach the most efficient resolution.
> The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by
> contacting Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscripti...t/default.aspx.
> ================================================== ==========
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== =======
>
>
>
>
>

Hi Jason,
You did a good summary.

A dbo also implies an user who creates the database. An user who is not
mapping to a login with fixed server role sysadmin, it can also be a dbo if
it is granted with CREATE DATABASE permission. Also dbo can be changed to
map another login by executing sp_changedbowner.

It is enough you just give the fixed server role sysadmin to your new group
of system admins.

Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======