SQL Server services and domain accounts / permissions

I wouldn't use that link as there have been changes with different versions (OS and SQL).  And despite what is said in the post, it is not the same on Windows Server 2008 and Windows Server 2008R2. Microsoft's documentation on this is pretty good (and includes some differences between Windows Server 2008 and Windows Server 2008R2).
Configure Windows Service Accounts and Permissions

What the posts you have read are talking about is that SQL Server itself will manage the permissions as long as you specify the service accounts during setup or using Configuration Manager. You don't need to specify the windows rights you listed in the example for SQL Server Agent. All you need to do is specify the service accounts using the correct tools - setup or Configuration Manager.
The service account will have one set of permissions and the virtual account (which is NT SERVICE\SQLSERVERAGENT) has another set of permissions. It doesn't get permissions from any of the builtin accounts. It has it's own set of permissions. So if someone does log into the server with the service account, they don't really have any elevated permissions as they used to in earlier versions of SQL Server. You generally never have to touch any of the permissions.
It only comes up with changing things after the installation - usually something like changing the backup directory to a different location. But the rights in windows and the setup for the permissions in SQL Server you wouldn't need to change as long as you changing the service account using Configuration Manager only.
It's a fairly convoluted thing on how all of these work together between the service account, the virtual account and the Windows group (which has also changed through the versions). In previous versions of SQL Server, the Windows group that the service accounts were in had more permissions granted for the account but now it's just in the Users windows group. They used to have a specific Windows group for SQL Server accounts. 

Sue

Hi Sue

Ok so what my plan is was to create 2 domain accounts; one for the engine and one for agent and set all instances on all the database servers with these accounts but don’t I need to create logins for them or just add them to the windows group login as a service because otherwise the services accounts give a login failed error when I change the service account from virtual accounts to the new custom accounts and our servers have custom builds with custom group policies so I fear the permissions weren’t given accordingly.

Anyways we’re using windows 2008R2 with Sql server 2008 / 2012 and Windows 2012 R2 with Sql server 2016 SP1.

Shall I use the custom accounts or stick to the virtual accounts?

I know MS recommends a different domain account for each service but do you need to make logins at the server security level or no need?

Kal

You don't set or do anything with virtual accounts. SQL Server manages that. I'm not sure but maybe your are confusing virtual accounts and built in accounts (local service, network service, local system). SQL Server service accounts are windows accounts, built in accounts or managed service accounts. Just use windows accounts as it's probably easier at this point.
If I want to create a new service account for SQL Server, the steps would be to first create the windows account and password. Then in SQL Server configuration manager, change the SQL Server logon account to the newly created windows account, enter the password, click apply and you get prompted to restart, restart and you are done.
You really want to leave things alone - it's pretty rare to have to set any permissions yourself. People generally have problems when they don't let SQL Server manage things or don't use configuration manager. Configuration Manager manages all of those things with permissions. If something goes weird from group policies then it's good to catch it so your group policy can be changed.

Sue

Yeah I meant built in accounts sorry.

I created two non admin domain accounts and changed the built in service accounts using config manager and they still gave an error of failed login even when I put the right password. Only after adding them to the group log on as a service was the change of accounts accepted.

I’ll remove their logins and see if it affects anything.

Thanks Sue

Kal

Log on as service is one of the rights in windows. Did you create a windows group called Logon as a service? There is no default group of Logon as a service

Sue

Hi Sue
Yeah apparently in our windows build there is a group which is called log in as a service to avoid giving admin privs to the users
Just to confirm your message above if i create a windows group in server manager called AppXGp and then i assign users to this group. Then i go to SQL Server on the server level security side i create a LOGIN called AppXGp and assign it db_datareader and db_datawriter then all the users in that group will have those permissions. Is this the correct logic?
SQL Server 2012 / 2016 will carry forward the permissions from SQL server and grant them to all the domain users in the windows group i created called AppXGp

please confirm
Kal

Mostly yes. Always yes if that is the only permission they have. Permissions are cumulative with deny taking precedence.
So if someone in that AppXGp group is in another group that has db_denydatareader then they have both data reader and deny data reader so deny takes precedence and they can't read the data. A user can be in multiple groups as well as have their individual permissions and all users have public permissions. You want to take all of those into consideration when figuring out a users permissions. 

Sue