Database Connections As SA

What this check looks for

Active sessions in sys.dm_exec_sessions where is_user_process = 1 and the login_name is sa, grouped by database with a count of connections.

Why it matters

sa is not an administrator account. It is the account that can do anything, cannot be restricted, and cannot be told apart from any other use of itself.

Three separate problems, and they compound:

Unlimited privilege. An application connecting as sa can drop any database, read any data, create logins, enable xp_cmdshell and reconfigure the instance. A SQL injection flaw in that application is not a data breach in one database; it is full control of the server and, depending on the service account, of the machine.

No accountability. Every audit trail, every sys.dm_exec_sessions row and every trace entry says sa. With three applications and two people all using it, nothing can attribute an action to any of them. After an incident, the question “who ran this” has no answer.

It cannot be rotated. Because the password is in several applications’ configuration files, nobody dares change it. So it stays the same for years, it is known to people who have left, and it is frequently in a source control repository.

And it is the first account attacked. sa exists on every SQL Server, so every automated attack tries it. That is why the blank password and weak password checks look at it, and why brute force attempts against it are the usual content of a failed login audit.

The usual reason it happens is not laziness. An application failed with a permission error during installation, somebody used sa to get past it, and it worked. The correct permission was never determined because nothing ever forced the question again.

How to confirm it yourself

Who is connected as sa right now:

SELECT s.[session_id],
       DB_NAME(s.[database_id]) AS [database_name],
       s.[login_name],
       s.[host_name],
       s.[program_name],
       s.[client_interface_name],
       s.[login_time],
       s.[status]
  FROM sys.dm_exec_sessions AS s WITH (NOLOCK)
 WHERE s.[is_user_process] = 1
   AND s.[login_name] = 'sa'
 ORDER BY s.[login_time];

program_name and host_name are the answer to the whole question. They identify which application and which machine, and that is what you need to fix it.

Summarised, which is what the check reports:

SELECT DB_NAME([database_id]) AS [database_name],
       [program_name],
       [host_name],
       COUNT(*) AS [connections]
  FROM sys.dm_exec_sessions WITH (NOLOCK)
 WHERE [is_user_process] = 1 AND [login_name] = 'sa'
 GROUP BY [database_id], [program_name], [host_name]
 ORDER BY [connections] DESC;

Sample it repeatedly. A single look catches what is connected at that moment; a job that runs nightly as sa will not appear unless you look while it runs.

The state of the account itself:

SELECT [name], [is_disabled], [is_policy_checked], [is_expiration_checked],
       [modify_date], [create_date]
  FROM sys.sql_logins WITH (NOLOCK)
 WHERE [principal_id] = 1;

principal_id = 1 finds it even if it has been renamed.

And what the application actually needs, which is the real work:

-- run while the application is working, then read what it touched
SELECT TOP (50) DB_NAME(qt.[dbid]) AS [database_name], qt.
  FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
 ORDER BY qs.[last_execution_time] DESC;

How to fix it

Give each application its own login with the permissions it actually needs. That is the whole fix, and the work is determining “actually needs”.

1. Identify each consumer from program_name and host_name.
2. Create a login per application, not one shared one:

CREATE LOGIN [app_Orders] WITH PASSWORD = 'a strong password', CHECK_POLICY = ON;
USE [OrdersDb];
GO
CREATE USER [app_Orders] FOR LOGIN [app_Orders];
ALTER ROLE [db_datareader] ADD MEMBER [app_Orders];
ALTER ROLE [db_datawriter] ADD MEMBER [app_Orders];

3. Start with db_datareader and db_datawriter, and add from there. Most applications need no more. If it calls stored procedures, grant execute on the schema rather than adding it to a broader role:

GRANT EXECUTE ON SCHEMA::[dbo] TO [app_Orders];

4. Resist db_owner. It is the second most common shortcut after sa and it means the application can drop its own tables.
5. Test properly. Run the application’s full cycle, including month end and any administrative function, because those are what fail later. The permission error will name exactly what is missing, which makes this iterative rather than guesswork.

Then deal with sa itself:

-- rename it, so automated attacks miss
ALTER LOGIN [sa] WITH NAME = [NotTheSaAccount];

-- and disable it
ALTER LOGIN [NotTheSaAccount] DISABLE;

Before disabling it, make sure you have another route in. At least one other sysadmin, and ideally a Windows group, so that disabling sa does not lock you out. Note that a disabled sa can still own databases and jobs, which is fine and is covered by the ownership checks.

Do this incrementally. One application at a time, with a rollback plan. Changing everything at once on a production instance is how an outage happens.

How long it takes

About four hours per application, most of it determining the minimum permissions and testing them. Doing it for an instance with several applications is a project rather than a task.

Related reports

Related checks

Frequently asked questions

Our vendor requires sa. Ask for the specific permissions in writing. A vendor who cannot say what their product needs has not looked, and most requirements turn out to be db_owner or less. If it genuinely needs sysadmin, a dedicated sysadmin login for that application is still better than shared sa, because it is attributable and revocable.

Is renaming sa worth anything? It stops the most basic automated attacks, which try the literal name. It is a small measure alongside disabling it, not a substitute.

Can I disable sa safely? Yes, provided another sysadmin exists and you have tested logging in with it. sa does not need to be enabled for anything, including owning databases and jobs.

Nothing is reported but I know something uses sa. The check looks at connections at that moment. A nightly job running as sa will not appear unless the scan runs while it does. Sample repeatedly.