Re: Groups for dbo

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 06/04/05

  • Next message: Dan Guzman: "Re: modifying database permissions" 
    Date: Fri, 3 Jun 2005 20:17:20 -0500

    > but looking ahead, I'm wondering about code like the following:
    >
    > IF Is_Member ('My Group Name') SET SomeFlag = 1
    >

    'dbo' is a special user that isn't usually used for routine database access.
    I'm not sure I understand your concern about IS(MEMBER and the 'dbo' user.
    The 'dbo' user has full permissions in the database anyway so I can't think
    of a scenario that would make role membership useful for 'dbo'. Perhaps it
    is appropriate to consider an IS_MEMBER always true for your purpose.

    IF Is_Member ('My Group Name') OR USER = 'dbo' SET @SomeFlag = 1

    > If changing the database owner is the best way to do that, which I don't
    > terribly like but it'll do, then how do I go about doing that? I can
    > probably find it in the docs somehwere, but I don't think I know how
    > off-hand.

    To change the database owner:

    USE MyDatabase
    EXEC sp_changedbowner 'SomeLogin' 

    -- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Morley" <rmorley@magma.ca> wrote in message 
news:O9jJWZGaFHA.3132@TK2MSFTNGP09.phx.gbl...
> Hi everyone,
>
> I'm still fairly new to SQL Server, and I'm trying to figure something 
> out: When I log on to SQL Server with Integrated Security (SSPI), it 
> figures out that I'm actually the database owner and logs me in as dbo 
> instead (i.e., user_name = 'dbo'; suser_sname = '<our 
> domain>\robert.morley').
>
> Under our current settings, that make no difference to me whatsoever, but 
> looking ahead, I'm wondering about code like the following:
>
>    IF Is_Member ('My Group Name') SET SomeFlag = 1
>
> It would seem that you can't change the group membership for dbo, so if 
> you have a custom group, Is_Member() always returns 0.
>
> I can see a couple of ways to work around this problem (changing the 
> database owner to some other account, creating a SQL Login just for me and 
> using that, etc.), but they all seem kinda messy solutions.  Does anybody 
> have any other suggestions as to how to work around this problem?  If I 
> could get SQL Server to not map my login to dbo, that would be the ideal 
> solution, but adding dbo to groups would be sufficient as well.
>
> If changing the database owner is the best way to do that, which I don't 
> terribly like but it'll do, then how do I go about doing that?  I can 
> probably find it in the docs somehwere, but I don't think I know how 
> off-hand.
>
>
>
> Thanks,
> Rob
>
  • Next message: Dan Guzman: "Re: modifying database permissions"

    Relevant Pages

    • Re: Will anyone know this?
      ... You can determine if your login is the database owner with sp_helpdb. ... The only required user in a SQL Server database is dbo and this special ...
      (microsoft.public.sqlserver.security)
    • Re: Confused about dbo
      ... Jasper Smith (SQL Server MVP) ... > database within a SQL Server instance. ... > There are several ways you could have the username dbo. ... > the true owner of the database. ...
      (microsoft.public.sqlserver.security)
    • Re: System Administrator Implied Permissions
      ... mapped to the 'dbo' user are members of the public and db_owner roles. ... Permissions are not checked for sysadmin role members so all other role ... > database role need to be assigned to any sysadmin role? ... >>> SQL Server initially creates the 'sa' user. ...
      (microsoft.public.sqlserver.security)
    • Re: Delegate Power of God to only 1 database - How?
      ... Guess I'll have to look more closely at the permissions ... >I support the Professional Association for SQL Server ... >> permissions to only that database which can be assigned ... >>>Exactly what is this 'dBO' role you are referring to? ...
      (microsoft.public.sqlserver.security)
    • Re: Change dbo
      ... I have a database where a user is defined as the dbo on a database. ... I tried using SQL Server Manger ... The login for this person maps to dbo. ...
      (microsoft.public.sqlserver.security)