cannot alter user 'dbo'

I am using SQL Server Express 2008.  I just created a new database called "TC3".  I am trying to map my login to this database, but getting the error "Cannot alter the user 'dbo'.

I go to the security >> logins node and double click my user login:
[MachineName]\[Username]

Under the Users mapped to this login I have the TC3 database checked with the user 'dbo' and default schema 'dbo'.  I try to change the user to [MachineName]\[Username] and get the above error message.

Why can't I map my user login to this database?

These are the same steps I used with SQL Server Express 2005 and never had any problem.  I'm not sure what is going on here.

Thanks for any help.
-Dman100-Software ConsultantAsked:
Who is Participating?
 
dbaSQLConnect With a Mentor Commented:
go into SSMS, run this:
alter authorization on database::techcenter to sa

then go back into Security\Logins, select DEPPS\dwayne
go into user mapping, you will see that 'dbo' is no longer listed as user for that database.
in fact, that database is no longer checked in user mappings

check it, put DEPPS\dwayne in the user, 'dbo' or 'dwayne' in default schema
hit ok
1
 
dbaSQLCommented:
SSMS, go into Server\Database\Security\Users -- double click your user, it brings up the 'Database User - username ' dialog box.  Uptop it shows Username, Loginname, a couple other items, and 'default schema'.  Assuming this says 'dbo', just put the username in here, and hit 'ok'.  

If user is a member of the dbo role, you may have to remove first, but probably not.
Also, please check to see if that user is the database owner.  If so, you'll need to change the database owner to a different login before you can do this.
1
 
-Dman100-Software ConsultantAuthor Commented:
Thanks for replying to my post and offering your help.

I followed the steps you indicated and got the following error message:

The login already has an account under ad different user name.

I cannot seem to associate my user to new databases I create.  Any thoughts on what else I can try?

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
dbaSQLCommented:
go into the Security\Logins at the server level.  double-click your login, go into 'User mapping', and see if there is already a user mapped to this login for this database.
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Dman, in SSMS choose the database you want and open a New Query. In the New Query window write and execute the follow:

DROP USER [MachineName]\[Username]
GO

EXEC sp_changedbowner '[MachineName]\[Username]'
0
 
Anthony PerkinsConnect With a Mentor Commented:
The system Stored Procedure sp_changedbowner is deprecated in SQL Server 2008, use
ALTER AUTHORIZATION insted.
0
 
-Dman100-Software ConsultantAuthor Commented:
'dbo' is the user mapped to the database that I'm trying to change to my login (see the attached screenshot).  That is when I get the cannot alter 'dbo' error.
screenshot.jpg
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You did what I've suggested?
0
 
-Dman100-Software ConsultantAuthor Commented:
thank you...that fixed it.  Why is the 'dbo' user mapped to a new database I create?  I never encountered this when using sql server express 2005.  Is this something new with sql server express 2008?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No. Depends how you created the database (and with that user permissions).
0
 
dbaSQLCommented:
not entirely sure how that happened on your end.  the database assumes ownership of the login that creates it.  i checked a test db on my test server -- it was still owned by my domain login.  

you run exec sp_helpdb , or right click database, choose properties -- you can see the owner.

anyway, i'm not exactly sure why, but that maps 'dbo' to the new database.

just alter authorization (or sp_changedbowner in the old world), set it to 'sa', as it should be (in my opinion)   then the 'dbo' mapping is gone, and you're good to go
0
 
-Dman100-Software ConsultantAuthor Commented:
thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.