system administrators role and dbo rights

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jason - 26 Mar 2008 15:31 GMT

I noticed that the built in admin account is given sysadmin role, but it is
also given dbo on every database. Isn’t this redundant? I thought sysadmin
role automatically gives a user dbo rights on any db it uses.

When I add the new group of dbas to replace the built-in admin group, I only
need to give it the sysadmin role, correct?

Reply to this Message

Tibor Karaszi - 26 Mar 2008 18:05 GMT

>I noticed that the built in admin account is given sysadmin role, but it is
> also given dbo on every database.

That last part doesn't make sense to me. Dbo is the login that owns a database. So, you can't "give
someone dbo". Either you mean that some user is a member of the db_owner role or perhaps the GUI
show somebody who is sysadmin to also "be dbo" which would the GUI confusing us a bit.

> When I add the new group of dbas to replace the built-in admin group, I only
> need to give it the sysadmin role, correct?

Yes, correct. :-)

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>I noticed that the built in admin account is given sysadmin role, but it is
> also given dbo on every database. Isn’t this redundant? I thought sysadmin
> role automatically gives a user dbo rights on any db it uses.
>
> When I add the new group of dbas to replace the built-in admin group, I only
> need to give it the sysadmin role, correct?

Reply to this Message

Jason - 26 Mar 2008 21:53 GMT

Hi Tibor

To clarify: on the properties of the BUILTIN\Administrators login, on the
database access tab. I see a check box in the permit column for every db and
the user column shows “dbo”. However the actual dbo for the database is some
other login. This doesn’t seem correct.
Below in the database roles for ‘xyz’ section, the login has public and
db_owner checked for every db. This seems redundant, the
BUILTIN\Administrators has the system admin role, so doesn’t that db_owner
rights for each db is implied.

> >I noticed that the built in admin account is given sysadmin role, but it is
> > also given dbo on every database.
[quoted text clipped - 14 lines]
> > When I add the new group of dbas to replace the built-in admin group, I only
> > need to give it the sysadmin role, correct?

Reply to this Message

Tibor Karaszi - 27 Mar 2008 09:00 GMT

Ok, I see what is happening. As a sysadmin login, you are "considered" dbo everywhere you go. So
Enterprise Manager will display you as sysadmin even if you really aren't sysadmin. Does that make
sense?

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi Tibor
>
[quoted text clipped - 26 lines]
>> > When I add the new group of dbas to replace the built-in admin group, I only
>> > need to give it the sysadmin role, correct?

Reply to this Message

Jason - 28 Mar 2008 13:56 GMT

thanks tibor

> Ok, I see what is happening. As a sysadmin login, you are "considered" dbo everywhere you go. So
> Enterprise Manager will display you as sysadmin even if you really aren't sysadmin. Does that make
[quoted text clipped - 30 lines]
> >> > When I add the new group of dbas to replace the built-in admin group, I only
> >> > need to give it the sysadmin role, correct?

Reply to this Message

Charles Wang[MSFT] - 27 Mar 2008 04:31 GMT

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/subscriptions/managednewsgroups/default.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/subscriptions/support/default.aspx.
============================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message

Jason - 27 Mar 2008 14:20 GMT

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

> Hi Jason,
> In addition to Tibor's suggestion, I would like to add more comments for
[quoted text clipped - 69 lines]
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================

Reply to this Message

Charles Wang[MSFT] - 28 Mar 2008 10:28 GMT

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

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message