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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Vitor MontalvãoMSSQL 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 PerkinsCommented:
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
dbaSQLCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.