unable to change db owner   

On my box I have Sql Server 2005 installed in mixed-mode.

I created a db and by default ( I assume ) it's owner is set to my logged-on 
(local) Windows user.

I backup the db and successfully restore it onto another box with another 
instance of Sql Server 2005. ( 64bit but I don't think this matters ).

However, the database is inaccessible because my (local) Windows user 
doesn't exist on that box.

So, I go back to my box and attempt to change ownership to a Sql Server user 
and backup/restore again :


EXEC sp_changedbowner 'MyUser'



Msg 15110, Level 16, State 1, Line 1

The proposed new database owner is already a user or aliased in the 
database.

date: Mon, 28 Jul 2008 17:20:54 -0700   author:   John A Grandy johnagrandy-at-gmail-dot-com

Re: unable to change db owner   

Ahh, I found the solution :

sp_changedbowner [ @loginame = ] 'login'
    [ , [ @map = ] remap_alias_flag ]

Arguments
[@loginame =] 'login'

Is the login ID of the new owner of the current database. login is sysname, 
with no default. login must be an already existing Microsoft® SQL ServerT 
login or Microsoft Windows NT® user. login cannot become the owner of the 
current database if it already has access to the database through an 
existing alias or user security account within the database. To avoid this, 
drop the alias or user within the current database first.



"John A Grandy" <johnagrandy-at-gmail-dot-com> wrote in message 
news:%231ty3DR8IHA.3624@TK2MSFTNGP05.phx.gbl...
> On my box I have Sql Server 2005 installed in mixed-mode.
>
> I created a db and by default ( I assume ) it's owner is set to my 
> logged-on (local) Windows user.
>
> I backup the db and successfully restore it onto another box with another 
> instance of Sql Server 2005. ( 64bit but I don't think this matters ).
>
> However, the database is inaccessible because my (local) Windows user 
> doesn't exist on that box.
>
> So, I go back to my box and attempt to change ownership to a Sql Server 
> user and backup/restore again :
>
>
> EXEC sp_changedbowner 'MyUser'
>
>
>
> Msg 15110, Level 16, State 1, Line 1
>
> The proposed new database owner is already a user or aliased in the 
> database.
>

date: Mon, 28 Jul 2008 17:35:12 -0700   author:   John A Grandy johnagrandy-at-gmail-dot-com

Re: unable to change db owner   

On Mon, 28 Jul 2008 17:20:54 -0700, "John A Grandy"
<johnagrandy-at-gmail-dot-com> wrote:

>EXEC sp_changedbowner 'MyUser'

>Msg 15110, Level 16, State 1, Line 1
>
>The proposed new database owner is already a user or aliased in the
>database. 

Apparently you tried to assign a new owner by choosing an account that
is already a user in the database, and that is not allowed.  If you
must use MyUser then remove them as a user first.  Or you might just
assign sa as the owner, which is probably the least likely to cause
problems.

Roy Harvey
Beacon Falls, CT

date: Mon, 28 Jul 2008 20:36:49 -0400   author:   Roy Harvey (SQL Server MVP)