I was recently poking around our SQL Server (2014), and noticed the SQL Service account uses the LOCAL SYSTEM account. Are there any issues with using this account - Pros/Cons? Should we be using another account?
Users do often execute OPENROWSET and XP_CMDSHELL commands. Not sure if that if lower-permissioned accounts work with advanced functions.
Thanks!
As security best practice, do not use Local System:
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.
On the "pro" side of this would be that using high-privileged account means that it's less likely to encounter any permission issue - or wait, is that a con? :)
A domain account (to include g/MSA) is required in some circumstances like an FCI, but not all. For a stand-alone SQL Server install, virtual account is acceptable. Please read above linked doc for more info. To paraphrase for SQL Server on Windows:
If domain-joined, then use gMSA or MSA. If not domain-joined, then use virtual account.
Quoting same doc:
Always run SQL Server services by using the lowest possible user rights. Use a MSA, gMSA or virtual account when possible.
Those settings are not a best practice. But you may have to move some processes to SQL Agent or SSIS in order to change them. But using Linked server with drivers that require access to the desktop session, and running xp_cmdshell are both also not a best practice. So that would also be progress.
