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. NeophyteSQL 241 Reputation points
    2020-11-06T15:11:12.703+00:00

    the dbo user is associated with the owner of the database--I understand

    we have other databases where the owner of the database is a different login (x)

    however sa is still associated with dbo on the login properties, but for only this database, the sa cannot be linked to dbo because it says the user already exists

  2. NeophyteSQL 241 Reputation points
    2020-11-06T16:05:44.007+00:00

    sql server-security-logins-sa -usermappings-other databases have user and default schema listed as dbo.

    but when I try to select dbo for one of the databases, I am unable to map the sa login to the user dbo and default schema dbo unless the owner is made sa

    however, other databases have owner other than sa, but still the sa login maps to the user databases using the dbo user and default schema dbo.

  3. NeophyteSQL 241 Reputation points
    2020-11-06T16:58:42.883+00:00

    we have other databases where the sa is mapped to the user dbo although the database owner is not sa. It is a different domain account used to perform third party backups.

  4. NeophyteSQL 241 Reputation points
    2020-11-06T19:24:11.523+00:00

    the properties on the server principal sa go to user mappings

    for all the databases the sa is mapped to the database using dbo user and default schema is also dbo. although the databases are not owned by sa

    however, one of the databases, I am unable to map the sa login to user dbo and default schema dbo unless the database owner is changed to sa.

    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.