How to revoke "Alter role" on users

Question

I have a user "sa" which is sysadmin in a MSSQL instance. All users who log into the database have the permission to change the password and delete the "sa" user. Is there a way to revoke this permission?

Answer 1

You cannot prevent from sysadmin to change the sa password, but we can secure the SQL Server SA Login from few users.

Different ways to secure the SA login:

Use Windows Authentication Mode

One way of doing this is to use "Window Authentication mode" from the Server Properties dialog which will allow access to only Windows logins and not SQL logins. Microsoft also recommends Windows authentication mode only. One thing to note is that even though you are using Windows authentication, you must use a strong password for the SA account, because anyone can change the authentication mode by updating the registry value and restarting the SQL Services.

Disable the SA Login

Disabling the SA account is a good option to prevent its use. When it is disabled no one can use it in any circumstance until it is enabled. The only disadvantage is that we can't use the SA account in an emergency.

You can use the below T-SQL to disable SA account.

--Query to disable the SA account.
ALTER LOGIN sa DISABLE;

This query will check the status of the SA account. A value of 1 indicates the account is disabled and 0 indicates the account is enabled.

--Query to check account status
SELECT name,is_disabled from sys.server_principals where name='sa'

Rename the SA Login

You can also rename the SA account which will prevent hackers/users to some extent. The one disadvantage is that it does not change the SID for the SA account which by default is 0x01, so someone could find the new name by looking up the SID.

--Query to check account status
ALTER LOGIN sa WITH NAME = [mssqltip];

This query could be used to lookup the name for the SA account based on the SID.

SELECT * FROM sys.syslogins WHERE sid = 0x01

Answer 2

The only way other users have the ability to change the password of the SA account are other members of the sysdamin role and members of the security admin role. Are your users part of the sysadmin or security admin role?

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-2017