Re: Groups for dbo

From: Robert Morley (rmorley_at_magma.ca.no.freakin.spam)
Date: 06/05/05


Date: Sat, 4 Jun 2005 21:54:33 -0400

Hi Dan,

Thanks for the instructions on changing the database owner...I suspect
that's how I'm going to end up doing it.

While dbo is SUPPOSED to be a special user that's not used for routine
operations, it would seem that since I created the database, when I log into
it using Integrated Security, it sees me as being 'dbo' whether I like it or
not. Personally, I'd rather it left 'dbo' as strictly a SQL login and
treated me as <Domain>\robert.morley, but it seems it doesn't do that.

And yeah, I'd considered the possibility of adding an "...OR User=" clause,
but that violates my whole sense of the universe. :) One of the (many)
points of having groups is to avoid user-specific programming like that. In
any event, it's not a huge issue for now since, as I said, I don't have
anywhere in the database that I'm using anything like that right now, but it
seems awfully odd to me that you can't change group membership for dbo of
all users. It's probably safer to change the database owner in any event,
so I suspect that's what I'll do.

Thanks again,
Rob

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:O5Y0oMKaFHA.2520@TK2MSFTNGP09.phx.gbl...
>> 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
>>
>
>



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)