re map user dbo to sa login

NeophyteSQL 241 Reputation points
2020-11-05T17:29:34.363+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,788 questions
0 comments
Accepted answer
  1. Erland Sommarskog 111.1K Reputation points MVP
    2020-11-06T21:53:43.243+00:00

    the properties on the server principal sa go to user mappings

    38133-clipboard01.jpg

    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.

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. David Browne - msft 3,846 Reputation points
    2020-11-05T17:40:36.987+00:00

    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.

    1 person found this answer helpful.
    0 comments
  2. tibor_karaszi@hotmail.com 4,311 Reputation points
    2020-11-06T07:53:34.067+00:00

    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.

    1 person found this answer helpful.
    0 comments
  3. NeophyteSQL 241 Reputation points
    2020-11-05T17:50:44.167+00:00

    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.

  4. Cris Zhan-MSFT 6,626 Reputation points
    2020-11-06T07:05:06.507+00:00

    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://video2.skills-academy.com/en-us/dotnet/framework/data/adonet/sql/server-and-database-roles-in-sql-server#the-dbo-user-account

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.