MSSQL - Understand The Differences in All SQL User Accounts

To start and run, each service in SQL Server must have an account configured during installation. Startup accounts used to start and run SQL Server can be built-in system accounts, local user accounts, or domain user accounts.

Domain User Account

If the service must interact with network services, access domain resources like file shares or if it uses linked server connections to other computers running SQL Server, you might use a minimally-privileged domain account. Many server-to-server activities can be performed only with a domain user account. This account should be pre-created by domain administration in your environment.

Local User Account

If the computer is not part of a domain, a local user account without Windows administrator permissions is recommended.

Local Service Account

The Local Service account is a built-in account that has the same level of access to resources and objects as members of the Users group. This limited access helps safeguard the system if individual services or processes are compromised. Services that run as the Local Service account access network resources as a null session without credentials. Be aware that the Local Service account is not supported for the SQL Server or SQL Server Agent services. The actual name of the account is "NT AUTHORITY\LOCAL SERVICE".

Network Service Account

The Network Service account is a built-in account that has more access to resources and objects than members of the Users group. Services that run as the Network Service account access network resources by using the credentials of the computer account. The actual name of the account is "NT AUTHORITY\NETWORK SERVICE".

Local System Account

Local System is a very high-privileged built-in account. It has extensive privileges on the local system and acts as the computer on the network. The actual name of the account is "NT AUTHORITY\SYSTEM".

In addition to having user accounts, every service has three possible startup states that users can control:

·         Disabled The service is installed but not currently running.

·         Manual The service is installed, but will start only when another service or application needs its functionality.

·         Automatic The service is automatically started by the operating system.

The following table shows optional accounts for each SQL Server service, and the startup states for each service.

SQL Server service name	Optional accounts	Startup type	Default state following Setup
SQL Server	SQL Server Express: Domain User, Local System, Network Service All other editions: Domain User, Local System, Network Service1	Automatic1	Started Stopped only if user chooses not to autostart.
SQL Server Agent	Domain User, Local System, Network Service1	Manual1,2 Automatic only if user chooses to autostart	Stopped Started only if user chooses to autostart.
Analysis Services	Domain User, Network Service, Local Service, Local System1 4	Automatic1	Started Stopped only if user chooses not to autostart.
Reporting Services	Domain User, Local System, Network Service, Local Service	Automatic	Started Stopped only if user chooses not to autostart.
Integration Services	Domain User, Local System, Network Service, Local Service	Automatic	Started Stopped only if user chooses not to autostart.
Full-Text Search	Use an account different than the account for the SQL Server service. The account will default to Local Service on Windows Server 2008 and Windows Vista.	Automatic	Started Stopped only if an account is not specified on Windows Server 2003 or Windows XP.
SQL Server Browser	Local Service	Disabled3 Automatic only if user chooses to autostart.	Stopped Started only if user chooses to autostart.
SQL Server Active Directory Helper	Local System, Network Service	Disabled	Stopped
SQL Writer	Local System	Automatic	Started

http://msdn.microsoft.com/en-us/library/ms143504.aspx