Exchange of login for database user

I have a database with 2 users - 'dbo' and 'user1'.

Currently:
=> 'dbo' is tied to login - 'login1'
=> 'user1' is not tied to any login.

I want to change the login tied to the database users to:
=> 'dbo' tied to no login
=> 'user1' tied to 'login1'

I try to use:
 sp_change_users_logi
n 'Update_One', 'user1', 'login1'
But it give me an error said 'login1' is already tied to a user.

Then I try to remove the login tied to 'dbo' with this:
 sp_change_users_logi
n 'Update_One', 'dbo', ''
Then I get another error said that 'dbo' is a forbidden value for the
login name parameter.

------

Previously, all the while 'dbo' is not tied to any login and 'user1' is
tied to 'login1', but yesterday I restore the DB from the backup file
bring back from customer side, then I saw the login changed to the one
I mention above, I'm not sure whether is the DBA from customer side
change it or what, but is there any way for me to change the login back
to the state I want?

Thanks.



Peter CCH

Peter CCH (petercch.wodoy@gmail.com)  writes:
> I have a database with 2 users - 'dbo' and 'user1'.
>
> Currently:
>=> 'dbo' is tied to login - 'login1'
>=> 'user1' is not tied to any login.
>
> I want to change the login tied to the database users to:
>=> 'dbo' tied to no login
>=> 'user1' tied to 'login1'

dbo is always tied to a login, since dbo is the database owner. But
you can change database owner with sp_changedbowner. Once you've done
that, you can connect user1 to login1.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

I tried in this sequence:
1. sp_changedbowner 'user1'
2.  sp_change_users_logi
n 'Update_One', 'user1', 'login1'

But "2" still give me the same error:
-----------------------------
Server: Msg 15063, Level 16, State 1, Procedure  sp_change_users_logi
n,
Line 104
The login already has an account under a different user name.
-----------------------------

This is the view in database tree in Enterprise Manager when I click on
the user section (Databases > dataBaseName > Users), there's 2 users in
list.

Name         Login Name          Database Access
--------------------------------------------------------------------------
user1                                     Permit
dbo            login1                   Permit

Previously, for so many times I restore the backup DB obtained from
customer site to my server, it have this view

Name         Login Name          Database Access
--------------------------------------------------------------------------
user1         login1                   Permit
dbo                                       Permit

But yesterday once I restore it, the login1 is not tied to user1
anymore, instead, it tied to dbo.



Peter CCH

Peter CCH (petercch.wodoy@gmail.com)  writes:
> I tried in this sequence:
> 1. sp_changedbowner 'user1'
> 2.  sp_change_users_logi
n 'Update_One', 'user1', 'login1'
>
> But "2" still give me the same error:
> -----------------------------
> Server: Msg 15063, Level 16, State 1, Procedure  sp_change_users_logi
n,
> Line 104
> The login already has an account under a different user name.
> -----------------------------

Of course. Since login1 owns the database, login1 maps to the user
dbo, and cannot map to the user user1. You need to change to a different
owner, for instance sa.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

You mean change the DB owner to 'sa', then only maps 'login1' to
'user1'?
I don't really understand what you mean.

Like this?
1. sp_changedbowner 'sa'
2. sp_changeuserslogin 'Update_One', 'user1', 'login1'

Please correct me if I'm wrong.

Thanks a lot.



Peter CCH

Peter CCH (petercch.wodoy@gmail.com)  writes:
> You mean change the DB owner to 'sa', then only maps 'login1' to
> 'user1'?
> I don't really understand what you mean.

Yes. If you want login1 to map to user1 in the database, then someone
else must own the database than login1. The database owner always maps
to dbo.

> Like this?
> 1. sp_changedbowner 'sa'
> 2. sp_changeuserslogin 'Update_One', 'user1', 'login1'

Yes.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

I tried the code, it still give me the same error.
I just notice there's something different for current state and before
the problem exist state:

I expand the SQL Server tree node, Security > Logins
Then I open the properties page of "login1" and go to "Database Access"
tab.

This is what it looks now:

Permit         Database        User
------------------------------------------------
(checked)    DB1                user1
(checked)    DB2                user1
(checked)    DB3                dbo

All that 3 database have "public" and "db_owner" role checked.

Before the problem occured, it looks like this:

Permit         Database        User
------------------------------------------------
(checked)    DB1                user1
(checked)    DB2                user1
(checked)    DB3                user1

For "DB 3" ... last time "User" column is "user1", but now it had
changed to "dbo".

QUESTION: Is there any way for me to change the "dbo" at column "User"
back to "user1"?

By the way, I login to the SQL Server with "login1".

Thanks.



Peter CCH

Peter CCH (petercch.wodoy@gmail.com)  writes:
> I tried the code, it still give me the same error.
> I just notice there's something different for current state and before
> the problem exist state:
>
> I expand the SQL Server tree node, Security > Logins
> Then I open the properties page of "login1" and go to "Database Access"
> tab.
>
> This is what it looks now:
>
> Permit         Database        User
> ------------------------------------------------
> (checked)    DB1                user1
> (checked)    DB2                user1
> (checked)    DB3                dbo

Thus login1 is still the owner of DB3. Did you run sp_changedbowner in
DB3?

What does sp_helpdb say?

What does DB3..sp_helpuser say when run it in DB3?

> QUESTION: Is there any way for me to change the "dbo" at column "User"
> back to "user1"?


Yes, change the database owner of the database to anyone else by user1.
Then use  sp_change_users_logi
n, or simply drop and re-add user.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

OK, got you. Tested and working.

Now "user1" is tied to "login1".
But the user "dbo" still tied to a login, is this a must?
Cause last time the user "dbo" is not tied to any login.

Expanding SQL Server tree node, Database > (database name) > Users

Here is what it looks like now:

Name            Login Name           Database Access
---------------------------------------------------------------------------
user1             login1                    Permit
dbo                sa                         Permit


Last time, it is shown like below:

Name            Login Name           Database Access
---------------------------------------------------------------------------
user1             login1                    Permit
dbo                                            Permit


I try to use:
 sp_change_users_logi
n 'Update_One', 'dbo', ''
thinking of making the "dbo" have no login tie to it, but I got the
following error message:
 ====================
============
Server: Msg 15287, Level 16, State 1, Procedure  sp_change_users_logi
n,
Line 39
Terminating this procedure. 'dbo' is a forbidden value for the login
name parameter in this procedure.
 ====================
============


QUESTION: Is it possible to make the "dbo" have no login tie to it?

Thanks.



Peter CCH

Peter CCH (petercch.wodoy@gmail.com)  writes:
> Now "user1" is tied to "login1".
> But the user "dbo" still tied to a login, is this a must?

Yes. A database must be owned by someone.

> Cause last time the user "dbo" is not tied to any login.

You get some funny things going when you move a database from one server
to another, but this is anomaly that should be corrected.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp