dbo login mapping and db owner

Question

_{Tuesday, May 12, 2015 3:15 PM}

Hi there,

I have three databases -- test, test2 and prod. For all three, my database owner set to a specific domain user -- let's say "DOMAIN\appuser". When I r-click database properties, the correct owner displays (DOMAIN\appuser).

In test and test2, "DOMAIN\appuser" is mapped to a named user in the database -- "appuser".

In prod, "DOMAIN\appuser" is mapped to the "dbo" user. I need to change this so it maps to "appuser", but I get the message "Cannot alter the user 'dbo'. 

1) Security > r-click "DOMAIN\appuser" > Properties > User Mapping. Change User and Default Schema from dbo to appuser.

Error: Cannot alter the user 'dbo'.

2) Then I tried this:  Change the db owner to some other user. Go into Login Properties > User Mapping and change User and Default Schema from dbo to appuser. Then change the db owner back to "DOMAIN\appuser". But I get the error: "The proposed new database owner is already a user or aliased in the database."

All replies (4)

_{Tuesday, May 12, 2015 3:32 PM | 1 vote}

Try setting the owner do SA, then dropping your user.

eg

use AdventureWorks2014 goalter authorization on database::AdventureWorks2014 to SA
go
drop user [somedomain\someuser]
go
alter authorization on database::AdventureWorks2014 to [somedomain\someuser]

David

David http://blogs.msdn.com/b/dbrowne/

---

_{Tuesday, May 12, 2015 3:45 PM}

I tried that, but in the Logins > User Mapping, it still shows User/Default Schema as "dbo".

---

_{Tuesday, May 12, 2015 3:50 PM}

The dbo user stands for database owner. The owner of the database, always enters the database as the dbo user. So your user can not both own the database, and enter the database as the user domain\appuser.

So changing the owner to some other user (sa or anyone else), might be best the solution.

Other possible though less likely things you could do:

Your owner could impersonate the appuser by using EXECUTE AS user = 'domain\appuser' but that would have to be run all the time, so that's not a great choice as a general principal.

You could have permissions assigned to some user role, and make both the domain\appuser and dbo members of the role, and stop worrying about the appuser. But that would probably require changes to your app.

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

---

_{Monday, May 18, 2015 9:08 PM}

Thanks for the replies. I was investigating this on behalf of the system administrator who was working a tight migration schedule with a 3rd party vendor and wanted to make sure everything was set up with as little question as possible. The discrepancies in ownership between the test and prod systems raised a red flag, hence my line of questions. He went forward with the migration with the permissions as-is, and there were no issues.