Share via

sql server sa account disable

NeophyteSQL 241 Reputation points
2020-08-25T00:48:10.26+00:00

I am working on renaming and disabling the sa account
the sa account has been renamed to sa_tmp
the dbo user on all databases now show they are associated with the login sa_tmp
if I disable the login, how should I handle all the schemas owned by the user dbo in the respective databases.
should I transfer the schemas from dbo to a different user.
thanks a lot

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,852 questions
0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2020-08-25T01:36:55.483+00:00

    Hi sqlniks,

    No. You needn’t to transfer them.

    Renaming and disabling the sa account won't stop internal processes from being able to use the sa account. Therefore, if you have databases whose owners are sa, there isn't a problem. This is a good thing, because some databases, like master and tempdb, require the sa account as the owner. Also, having SQL Server Agent jobs owned by sa won't fail, either. The impersonation still works.

    Please refer to Best Practices to Secure the SQL Server sa Account.

    If the response helped, do "Accept Answer" and upvote it.

    Best regards,
    Cathy

    1 person found this answer helpful.
    0 comments
  2. NeophyteSQL 241 Reputation points
    2020-08-25T03:38:10.477+00:00

    Thanks , which means the database schemas owned by dbos are still owned by the sa.
    but we are trying to ensure non system objects do not depend on sa
    so do we transfer the dbo owned schemas to a regular sql account

    0 comments
  3. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2020-08-25T06:40:04.347+00:00

    Hi sqlniks,

    > but we are trying to ensure non system objects do not depend on sa

    so do we transfer the dbo owned schemas to a regular sql account

    If non system objects depend on the account that is a member of sysadmin group, you still needn’t transfer them. If not, you can change it from SSMS UI, login properties> User Mapping

    Best regards,
    Cathy

    0 comments
  4. tibor_karaszi@hotmail.com 4,301 Reputation points
    2020-08-25T06:49:55.833+00:00

    An object is owned by a user, not a login. Whenever you act as sysadmin, you operate as dbo in all databases. A database is owned by a login. What you might want to consider is to change the owner of the database to somebody who isn't "the real sa", i.e., sid 0x1. Erland, for instance, proposes IIRC to create one login per database, and the sole purpose for that login is to own that database.

    0 comments
  5. NeophyteSQL 241 Reputation points
    2020-08-25T14:00:06.603+00:00

    thanks , is it ok for schemas inside the databases to be owned by dbo, and dbo user in turn is related to the sa login

    0 comments