In most of the non-production instances, the SQL Server service is configured to start using the Local System account. This account gets access to the SQL Server instance through the BUILTIN\Administrators group. Whoever is part of the Administrators group on the Server is part of this group. In SQL Server 2000 & SQL Server 2005, by default the BUILTIN\Administrators group is added to the sysadmin server role on the SQL Server instance. To restrict access to the Local System Administrators on the SQL Server Instance, the following action needs to be performed.

IMPORTANT NOTE: By default in SQL Server 2008 and 2012, there is no default access for Windows administrators to a SQL Server. For a Windows administrator (i.e., someone who is either a Domain Administrator or a Local Administrator) to have access, their login needs to be explicitly granted access or the group they belong to granted access along with rights within SQL Server itself.

The service account of SQL Server/Agent service needs to be granted requisite permissions on the SQL Server instance. If the SQL Server/Agent service is starting using Local System account, it needs to be changed to start using a Domain Windows account.

The sysadmin privileges for the BUILTIN\Administrators group needs to be revoked.

Create a New Login in SQL Server for the Service Account

A new login needs to be created in SQL Server for the service account. Since we know that the server where this will implemented is not so critical, it is safe to add the SQL Server service account to the Administrators group on the Server. The same account can be used to start the SQL Server Agent service or a new login with sysadmin privileges on the instance can also be created.

This login can also be created by executing the below command:

USE [master]
GO
CREATE LOGIN [DOMAINAccountName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = 'DomainAccountName', @rolename = 'sysadmin'
GO

Change the Service Account for the SQL Server service

Once the login for the Service Account has been created on the SQL Server instance, we can go ahead and change the SQL Server service to start with the new account.

Open SQL Server Configuration Manager and change the SQL Server & Agent service account to a Domain Windows account as in this screenshot.

Similarly change the SQL Server Agent to start with the new account. After making the changes, the SQL Server and Agent services needs to be restarted.

Revoking sysadmin privileges for BUILTINA\dministrators login

Before proceeding further, make sure that the sa login is enabled and you know the sa password. In SSMS open the Properties of BUILTIN\Administrators login and uncheck the sysadmin server role.

Or execute the below script in a new query window:

EXEC master..sp_dropsrvrolemember
@loginame = N'BUILTINAdministrators',
@rolename = N'sysadmin'
GO

Enabling sa access

Since the requirement is to enable sa login to access the instance, SQL Server must be configured for SQL Server and Windows Authentication mode authentication. This can be configured in the SQL Server Properties screen.

After making this change, the SQL Server service needs to be restarted for the changes to take effect. Now the SQL Server instance is configured to accept SQL Server authenticated logins.

From now on none of the system administrators on the server would be able to logon to SQL Server and sa is the only login who will have access to the instance.