Re: System Administrator Implied Permissions
From: Jose Molina (jmolina4@cox.net)
Date: 03/31/03
- Next message: Jeff Wellman: "update window"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Jose Molina" <jmolina4@cox.net> Date: Mon, 31 Mar 2003 07:43:41 -0800
Hi Dan,
Thanks again.
-Jose
>-----Original Message-----
>> Hi Dan,
>> Thanks for the clarification. Just to make sure I
>> understand you correctly: when sql server 'creates' the
>> sa login, it assigns it the System Administrator fixed
>> server role.
>
>Correct.
>
>> It also assigns it to the public role of
>> all databases (understandable) and has the boxes checked
>> for the db_owner fixed database role (which is what I am
>> questioning). If I understand what you are saying, then
>> the literal assignment to the db_owner role is redundant
>> in this case because as an administrator, you are a
>> member of the dbo group and you are automatically given
>> db_owner rights to every database(whether or not the
>> db_owner box is checked).
>>
>> Whew... lots to say...
>>
>> Now, given this (if indeed true), why does SQL Server
>> display this redundant information? Is there something
I
>> am missing?
>
>You are right that the reported information is
redundant. The dbo user
>in each database is always a member of the public and
db_owner roles.
>SQL Server shows that the sa and BUILTIN\Administrators
logins have
>access to all databases via the dbo user mapping and
reports role
>membership accordingly. However, these logins were not
explicitly added
>to those roles. Other sysadmin role members have the
exact same
>privileges but aren't reported as public and db_owner
role members for
>some reason.
>
>The bottom line is that sysadmin role members implicitly
have full
>permissions regardless of reported role membership.
>
>--
>Hope this helps.
>
>Dan Guzman
>SQL Server MVP
>
>-----------------------
>SQL FAQ links (courtesy Neil Pike):
>
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>-----------------------
>
>"Jose Molina" <jmolina4@cox.net> wrote in message
>news:03af01c2f72f$95fb6780$3301280a@phx.gbl...
>> Hi Dan,
>> Thanks for the clarification. Just to make sure I
>> understand you correctly: when sql server 'creates' the
>> sa login, it assigns it the System Administrator fixed
>> server role. It also assigns it to the public role of
>> all databases (understandable) and has the boxes checked
>> for the db_owner fixed database role (which is what I am
>> questioning). If I understand what you are saying, then
>> the literal assignment to the db_owner role is redundant
>> in this case because as an administrator, you are a
>> member of the dbo group and you are automatically given
>> db_owner rights to every database(whether or not the
>> db_owner box is checked).
>>
>> Whew... lots to say...
>>
>> Now, given this (if indeed true), why does SQL Server
>> display this redundant information? Is there something
I
>> am missing?
>>
>> Much Thanks Dan.
>>
>> -Jose
>>
>>
>> >-----Original Message-----
>> >Jose, I think I understand your question better now.
>> When you view
>> >login security in Enterprise Manager, the GUI will show
>> that logins
>> >mapped to the 'dbo' user are members of the public and
>> db_owner roles.
>> >This means that the 'dbo' user is a member of the
>> db_owner role and
>> >'dbo' (like all users) is a member of the public role.
>> >
>> >Permissions are not checked for sysadmin role members
so
>> all other role
>> >memberships and permissions are irrelevant
>> >
>> >--
>> >Hope this helps.
>> >
>> >Dan Guzman
>> >SQL Server MVP
>> >
>> >
>> >"Jose Molina" <jmolina4@cox.net> wrote in message
>> >news:007b01c2f6ef$f3b5f4c0$a201280a@phx.gbl...
>> >> Hi Dan,
>> >> I am aware of this but I still need a little more
>> >> clarification. As an example: Does the db_owner
fixed
>> >> database role need to be assigned to any sysadmin
role?
>> >> I was under the impression that this role was
implied.
>> >>
>> >> Please advise.
>> >>
>> >> Thanks!
>> >>
>> >> -Jose
>> >> >-----Original Message-----
>> >> >> SQL Server initially creates the 'sa' user. Isn't
>> the
>> >> >> db_owner an implied permission for the fixed
server
>> >> role
>> >> >> System Administrator anyway? If this is the case,
>> why
>> >> >> the redundancy?
>> >> >
>> >> >Members of the sysadmin role are automatically
mapped
>> to
>> >> the 'dbo' user
>> >> >in all databases. This is somewhat different that
>> being
>> >> a db_owner
>> >> >fixed database role member because the 'dbo' user
can
>> >> also perform
>> >> >functions on the owned database outside the database
>> >> context, such as
>> >> >DROP DATABASE.
>> >> >
>> >> >--
>> >> >Hope this helps.
>> >> >
>> >> >Dan Guzman
>> >> >SQL Server MVP
>> >> >
>> >> >-----------------------
>> >> >SQL FAQ links (courtesy Neil Pike):
>> >> >
>> >> >http://www.ntfaq.com/Articles/Index.cfm?
>> DepartmentID=800
>> >> >http://www.sqlserverfaq.com
>> >> >http://www.mssqlserver.com/faq
>> >> >-----------------------
>> >> >
>> >> >"Jose Molina" <jmolina4@cox.net> wrote in message
>> >> >news:002601c2f68b$0fce98c0$3401280a@phx.gbl...
>> >> >> Hello,
>> >> >> I had a question concerning the database
permissions
>> >> SQL
>> >> >> Server checks for the 'sa' role. The db_owner
fixed
>> >> >> database role is checked for each of the databases
>> when
>> >> >> SQL Server initially creates the 'sa' user. Isn't
>> the
>> >> >> db_owner an implied permission for the fixed
server
>> >> role
>> >> >> System Administrator anyway? If this is the case,
>> why
>> >> >> the redundancy?
>> >> >>
>> >> >> Thanks!
>> >> >>
>> >> >> -Jose
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>
>.
>
- Next message: Jeff Wellman: "update window"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
