TL;DR — Resumo Rápido
Corrija o erro do SQL Server Cannot alter the user dbo ao alterar a propriedade do banco de dados. Resolva o mapeamento de login usando sp_changedbowner e ALTER AUTHORIZATION.
Nota: Este artigo foi publicado originalmente em 2016. Alguns passos, comandos ou versões de software podem ter mudado. Consulte a documentação atual de SQL Server para as informações mais recentes.
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.
Solução
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:: TO ;