Sql-server – SQL Server Service Account – Things to test post change (from Local System to Network Service)

sql serversql-server-2008-r2sql-server-2012

We needed to change the SQL Service Account for the SQL Server Database Engine & Agent Services from Local System as it has extensive predigests.

What are the things that we should do to test within the SQL Server database itself to ensure that there are no issues / permission problems with this change?

Note the service has been changed, and seems to be running fine Both Agent Services / SQL Server Engine.

The two versions we are implementing this in are SQL Server 2008 R2 and SQL Server 2012.

Best Answer

In Short: Everything and Anything. And Nothing.

But seriously: A few thoughts about changing the SQL Server service account:

  1. ALWAYS use the SQL Server configuration manager. This tool is smart. It knows what permissions need to be assigned to the service account. And generally does it. If you change it from windows services control panel - GOOD LUCK.
  2. The service account is how SQL Server interacts locally and to the network. So moving from local system - you shouldn't care about breaking networked connections - since they wouldn't work anyway. But you would generally want to make sure things like "Will SQL Server start?" happen (that means it can at least read the drives that master/model/msdb/tempdb and the error logs are on). And that verifies the registry.
  3. If you assigned Perform Volume Maintenance Tasks to a SQL Server service account before - that will likely need to be assigned again.
  4. Generally this is a safe move. As long as you do it through the SQL Server configuration manager. Often we'll see folks do this through windows services control panel, something breaks, so they quickly grant their service account local admin rights - and "poof!" everything works.

In your particular case though - you are moving from one special privileged account to another. As you have seen - you were safe and things worked. I would also take this time to question the choice of the service account.

Are you on a domain? Why not use a domain user account per the best practices? The local accounts with special privileges are not the most secure or reliable service account choices. This article from Microsoft explains more there.

Related Question