SQL Server - How to disable “sa” login minimizing impacts

Reading Time: 6 minutes

Speak, my dear readers!
In this article, I would like to extend the security theme a little and share with you how to disable the “sa” login minimizing impacts. As I had already commented in the article SQL Server - How to enable / enable sa user, it is a good security practice to keep the “sa” user deactivated and renamed, since that user is in the role sysadmin (cannot be removed), cannot be deleted from the instance and is a standard user, that is, is present on any instance of SQL Server, making that user the preferred target for potential attackers. In fact, this is one of the security items of the FREE SQL Server Checkup that Fabrício Lima is offering to want to know the company.

For a few days I left public for the Web, an instance of SQL Server and Power BI Report Server to do some testing. After a few days, I saw in the SQL Server error log that they were already trying to break into my base. Imagine which user you tried most to use to access my bank?

Want to see how these statistics look in your instance? Just run the script below:

Now that I have briefly summarized why this user should be disabled and renamed, I will explain how we can do this in your database. In theory, it is extremely simple:

Or even through the Management Studio interface (SSMS):

The problem is that many people are insecure to do this operation in their database and end up impacting the environment. My idea in this post is to help you identify and minimize these potential impacts so you can apply these security settings to all of your environments.

Application using SA

The first item that should be checked is whether there is any application that uses the “sa” user to connect to the database (believe me, it exists). The easiest way to identify this is to ask the application vendor or the development team.

One way that can help you validate this information and even be able to identify if this scenario is occurring in your environment is by consulting DMV sys.dm_exec_sessions:

Result:

Databases where SA user is owner

This is a very common concern among DBA's that if you deactivate the SA user and rename it, there may be a problem in the databases where the SA user is the owner. However, you can rest assured that there is no problem when doing this. I have already done several tests in various environments, both testing and production, and disabling the SA user has no impact on that.

To prove what I am saying, here is a demonstration:

Result:

Jobs where SA user is owner

Just as when the SA user is the owner of a database, there is no impact regarding the execution of SQL Agent Jobs when renaming and disabling the “sa” login. They can be carefree and don't need to leave changing the owner of all jobs to rename / disable sa.

Query used for demonstration:

Result:

Linked Server

This is probably the most difficult validation to do in the environment, although it is also quite unusual. If there is a Linked Server that points to your instance, where the connection to the bank is made using the “sa” user, fixed in the LS, you may have problems deactivating and renaming that user.

This is difficult to validate because you have to go into all instances of your environment that might have some linked server pointed to that particular instance and validate if this scenario is occurring:

It is not very common to use Linked Server with a fixed user in the connection, since any user in the remote instance could execute ANY COMMAND in the target instance, since the connection is arriving as “sa”, which is sysadmin, but it is a validation that must be done to ensure that the change will not impact.

You can use this query to make this check easier:

Objects with IMPERSONATE using SA

It is quite common to see procedures and objects that use the EXECUTE AS command to perform tasks as if they were other users, especially to allow a low-privileged user to execute routines and commands that would require more privileges than they have, thus elevating them. of privilege. If you would like to dig deeper into IMPERSONATE and elevation of privilege, I recommend reading my article. SQL Server - How to use EXECUTE AS to execute commands as another user (Impersonate login and user).

To reassure you, know that it is not possible to use EXECUTE AS USER and impersonate the “sa” user, as he is a “special” user, that is, less of a concern for you.

Other services

And lastly, so that you can make your change in peace of mind, I recommend that you validate whether there are any Windows services, Reporting Services reports, any Power Shell routines, monitoring tools, or any processes outside of SQL Server that might be using the sa user in your environment.

As they are external processes and can be specific to your company, there is not much “cake recipe” to deal with these situations, but it is not at all common for a DBA to allow a service to use the “sa” user for such operations, so it must be very difficult to find this scenario.

Rest assured and confident when deactivating and renaming the “sa” user, as this is a good security practice for your environment. Remember to monitor the SQL Server log after this change to see if login failure messages using "sa" are occurring. I made available a query that can even automate this at the beginning of this post 😉

If you identify a problem, you may have discovered a hacking attempt or a non-standard process in your company that must be urgently changed to use another user to connect to the database.

Well guys, that's it!
I hope you enjoyed this tip, rename and disable the “sa” user in your environments and see you next time!