How to Disable Sa Account in Sql Server

question

sql server sa account disable

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-general

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

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

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

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.

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

I understand that. It doesn't invalidate anything I said. If you change the ovner of the database to somebody else than sa, then the schema vill be owner by dbo, but dbo will no longer point to sa.

Tibor,

Thanks for your answer.

However I understand the database owners ,the database owners can be a different login. the schemas of the database are owned by dbo, do they need to be transferred to some other login so sa is not used indirectly.

Let me be perfectly clear.

You do not need to do anything to the database owner or schema owner. Everything will work just fine when you disable "sa_tmp". The user must exist, not be "enabled".

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

The dbo schema must be owned by dbo. Other schemas do not have to be, but you would only change the schema owner if there is a special reason.

The database on the other hand is a different story. It is not good practice to have it owned by a sysadmin account. As Tibor says, my recommendation is that you create a unique SQL login which only exists to own that database. If the database is called Telephones, the login would be Telephone$owner or something like that. That login would be disabled and not granted any permissions.

question details

2 people are following this question.

Related Questions

How to Disable Sa Account in Sql Server

Source: https://docs.microsoft.com/answers/questions/75704/sql-server-sa-account-disable.html

Share this post