Exchange of login for database user

[Peter CCH]

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

[Erland Sommarskog]

Peter CCH (pe************@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, es****@sommarskog.se

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

[Peter CCH]

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

But "2" still give me the same error:
-----------------------------
Server: Msg 15063, Level 16, State 1, Procedure sp_change_users_login,
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

[Erland Sommarskog]

Peter CCH (pe************@gmail.com) writes:

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

But "2" still give me the same error:
-----------------------------
Server: Msg 15063, Level 16, State 1, Procedure sp_change_users_login,
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, es****@sommarskog.se

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

[Peter CCH]

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

[Erland Sommarskog]

Peter CCH (pe************@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, es****@sommarskog.se

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

[Peter CCH]

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

[Erland Sommarskog]

Peter CCH (pe************@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_login, or simply drop and re-add user.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Peter CCH]

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_login '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_login,
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

[Erland Sommarskog]

Peter CCH (pe************@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, es****@sommarskog.se

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

[Peter CCH]

Thanks for the info.
But I'm curious on one thing, since a database must be own by a user,
how come when I try to create a new database, the "dbo" user is not
tied to any login?

I tried create a new database, then expand the tree node and look at
the "Users" section, then I saw this:

Name Login Name Database Access
-----------------------------------------------------------------------
dbo Permit

Above is the "Users" section of a newly created database on my PC, for
the purpose of to confirm whether a user must tied to a login, I just
wondering why the "dbo" is not tied to any login.

Any reason in behind?

Thanks.
Peter CCH

[Erland Sommarskog]

Peter CCH (pe************@gmail.com) writes:

But I'm curious on one thing, since a database must be own by a user,
how come when I try to create a new database, the "dbo" user is not
tied to any login?

I tried create a new database, then expand the tree node and look at
the "Users" section, then I saw this:

Name Login Name Database Access
-----------------------------------------------------------------------
dbo Permit

Above is the "Users" section of a newly created database on my PC, for
the purpose of to confirm whether a user must tied to a login, I just
wondering why the "dbo" is not tied to any login.

It appears that you only see a login name, if this is an SQL Server
login. So if the database is owned by a Windows login, you don't see
this. The same thing happens if you say "sp_grantdbaccess DOMAIN\user"
to permit access for a windows login, the LoginName column is NULL.

But that is only the trick of the eye. If you would try to add the
Windows user that owns the database, you would be told that is is
already there.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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