Re: System Administrator Implied Permissions

From: Jose Molina (jmolina4@cox.net)
Date: 03/31/03

  • Next message: Don Dumitru [MSFT]: "Re: Pesky Pop Ups" 
    From: "Jose Molina" <jmolina4@cox.net>
Date: Sun, 30 Mar 2003 18:45:27 -0800

    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: Don Dumitru [MSFT]: "Re: Pesky Pop Ups"

    Relevant Pages

    • Re: Minimum Permissions for sp_adduser
      ... Yes he did say he was db_owner, per my first suggestion. ... But it turns out that being a member of db_owner is not enough, even though it should be, because it does not map a login to the "dbo" user. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
      (microsoft.public.sqlserver.security)
    • Re: Table ownership
      ... Perhaps the user isn't dbo, but is only a member of db_owner? ... specified the owner when the object is created: ... > reason on one SQL Server this is not the case. ...
      (microsoft.public.sqlserver.server)
    • Re: System Administrator Implied Permissions
      ... Hi Dan, ... database role need to be assigned to any sysadmin role? ... >> SQL Server initially creates the 'sa' user. ... >fixed database role member because the 'dbo' user can ...
      (microsoft.public.sqlserver.security)
    • Re: System Administrator Implied Permissions
      ... >> Hi Dan, ... >> Now, given this, why does SQL Server ... However, these logins were not ... Other sysadmin role members have the ...
      (microsoft.public.sqlserver.security)
    • Re: cannot acees two databases as owner
      ... it does not matter you are a member of the ... the sysadmin fixed server role or if there is no any other Login which is ... group if it's a domain) and you can login to your SQL Server with, ...
      (microsoft.public.sqlserver.setup)