Re: System Administrator Implied Permissions
From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 03/31/03
- Previous message: Kevin Goff: "Solitare Game"
- In reply to: Jose Molina: "Re: System Administrator Implied Permissions"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Dan Guzman" <danguzman@nospam-earthlink.net> Date: Mon, 31 Mar 2003 08:24:53 -0600
> 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 > >> > > >> > > >> >. > >> > > > > > > >. > >
- Previous message: Kevin Goff: "Solitare Game"
- In reply to: Jose Molina: "Re: System Administrator Implied Permissions"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
