¿Privileges on the Service SID or on the SQL Service Account?
On SQL Server 2008 the concept of Service SID was introduced to be used when installing on Windows 2008 or later.
The concept of Service SID (also known as Virtual Account) allows assigning the required permission needed for the correct functioning of the SQL Instance without using Local Groups or Domain Groups (in a cluster scenario). Instead, the permission will be assigned to the Service SID.
Just to be clear, the concept of Service SID is no from SQL Server, but form Windows Server 2008 or later, and SQL Server 2008 or later can take advantage of this feature.
The interesting part when configuring permissions or rights like “Lock Pages in Memory” or “Perform Volume Maintenance Tasks”, historically they were assigned to the SQL Service account. But now that the Service SID come into play, whom should I assign the rights? Similarly, if I want to do a backup on a network share, whom should I assign the permission on the share, so SQL Server can write the backup.
The same questions apply to any other privilege that before should be assigned to the Service account.
The answer is, that you can assign the privilege or permission to any and SQL Server will work fine. However, because the Service SID have the scope of only the computer where you installed SQL, to assign permission outside the computer you have to use the computer account, the name format is “DomainName\ComputerName$”.
In conclusion the privileges for SQL Service is the union of the privileges from the Service SID plus the privileges from the Service account.
Now that you know you can assign the privileges to both accounts, the service account or the Service SID, Which one should we use?, because the service account can be changed, the best practice is to assign the privilege to the service SID whenever possible.
To be clear on the concept of Service SID, for those who don’t have experience with it, check the following images. The account “NT Service\MSSQLServer” is the Service SID. The name of the service SID will include the instance name as part of the name, for example “NT Service\MSSQL$Denali”. In the images is shown how to add a Service SID to the privilege Lock Pages in Memory and the permissions for a folder.
The use of the service SID are extended on SQL Server 2012 and also exists the concept of Managed Service Account starting on Windows 2008, but those are concepts for other post.
References
https://support.microsoft.com/kb/2620201/en-us