Resolved: Cannot alter user ‘dbo’

Resolved: Cannot alter user ‘dbo’

I was working on setting up a new Microsoft SQL Server 2016 instance and importing databases from the old server when I ran into this issue. During the import (Attach) process, I assigned the user the application uses to connect as dbo (The application requires dbo rights btw… just saying before anyone says what a great best practice that is, lol.) The problem I ran into is that I mapped my user to dbo when I needed just to assign those rights and dbo as the default schema. Figured I would map another user to dbo but that’s when I started getting the “Cannot alter user ‘dbo'” error. I was like fine, let’s delete the user, err “Cannot alter user ‘dbo'”. Ok, let’s detach the database and import it back in, err “Cannot alter user ‘dbo'”. Ok, let’s rename it, err “Cannot alter user ‘dbo'”… I guess you get my point now.

Solution

From what I understood, dbo being the owner of the database is somehow protected. This is what causes all the restrictions on making any changes to that user… so if you can’t make changes to it then what are you supposed to do? Well, there is a SQL statement you can execute against your database that would change (oh wait, I thought we couldn’t make changes… well, you can’t but you can do this one change using this one method) the owner of the database effectively changing the dbo mapping to another user.

Here is the command, e sure to replace the information within the brackets [] to your appropriate database and user.

ALTER AUTHORIZATION ON database::[<yourdb>] TO [<your_username>];