re map user dbo to sa login

Question

one of my database "abc" has a owner "x"

the "dbo" user gets automatically mapped to the owner of the database.

I would like the dbo user to be remapped to the "sa" login , how to do it without changing the owner of the database to sa

Answer 1

the properties on the server principal sa go to user mappings

So this is the form you are talking about?

As you can see, it seems here that sa does not map to anything at all in any database. However, I can assure you that sa still maps to dbo in all these databases.

I think this is a good example of that UIs only make you dumb. As have been said repeatedly in this thread: all users who are in the sysadmiin role maps to the user dbo in every database.

Editing the database mappings for sa in that form is nonsense, and any name you enter yields an error message - just like it should. The only way you can create a mapping that displays in this form is to make sa owner of the database - and which you should not do in my opinion, that is not good practice.

Answer 2

sa and all members of the sysadmin fixed server role always connect to user databases as dbo. So there should be no need to change the database owner.

Answer 3

I agree with the other replies. To phrase it differently:

You can have only one login being the dbo. That is indeed the database owner.

But, and aspect of being member of the sysadmin role (which sa is) is that you operate as the dbo user in all databases.

Answer 4

but what if I want to change the database owner to domain\Y and still ensure dbo is mapped to sa.

if I change the database owner to be domain\y, I see that the Sa login is not mapped to the dbo user .

not sure what is the cause of the behavior.

irrepsctive of the owner, I want sa to be mapped to dbo user, but when owner changes, the sa does not get mapped to dbo. instead, the dbo user is mapped to that owner.

Answer 5

Hi @NeophyteSQL ,

>I would like the dbo user to be remapped to the "sa" login , how to do it without changing the owner of the database to sa

You cannot make it without changing the owner of the database. The Login name not allowed to modify in SSMS(Databases >Security>Users>dbo>Properties>General) , it is only the database owner.

You don't have to set the database owner sa to get the mapping done between sa and dbo -The dbo is a user account that has implied permissions to perform all activities in the database. Members of the sysadmin fixed server role are automatically mapped to dbo.
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/server-and-database-roles-in-sql-server#the-dbo-user-account