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
>>
>
>