Sql-server – Giving file system access to the SQLSERVERAGENT virtual account

So, firstly have a quick look through this which explains virtual accounts:

TechNet: Service Accounts Step-by-Step Guide

To answer your questions:

1/ You can't list these virtual accounts in that dialog box, I'm not 100% sure of the reason, but you always need to type them in manually, in your screenshot you also have the active directory domain listed, you would never see them in here as they are local accounts.

2/ You can't add permissions on files/shares on another machine by finding these accounts, as they live only on the machine that you installed SQL server on (NVOSIB-1C in your case). These accounts though can access resources on the network, but they do so in the context of the machine account of the server.

So, an example would be you have NVOSIB-1C with SQL Server installed and NVOSIB-Filer with a share on it that you want the SQL Server account to be able to access.

What you would do in this situation is to add the machine account of the SQL server to the permissions on the file share, you can do this just by assigning the account DOMAINNAME\NVOSIB-1C$ the permissions you want and then as the virtual account accesses the network in the context of the machine it lives on, it would have permission to do so.

Obviously this limits your ability somewhat to assign permissions, as anything running on NVOSIB-1C that authenticated in the context of the server would now have access to the share, if this is a concern, you can do one of 2 things:

• Use standard domain service accounts for the SQL Server services, you then need to maintain passwords etc for these, but it makes assigning permissions somewhat easier.

• Set up a managed service account in AD, this has the advantage that the local accounts have in that you don't need to worry about passwords, and also the advantage that you can see and manage them centrally in AD like a normal account, the main disadvantage is that you need to have the managed service account created before you install SQL server, so in your case you would have to reinstall to take advantage of this.

On the maintenance plans

I typically let my maintenance run as the agent account and I normally keep them owned by SA so when I quit or became a consultant things still run. When I help a client with maintenance/management I do the same thing. You see to run maintenance you need more than just CRUD permissions. You are doing backups (backup operator), index rebuild and statistics updates (DDL Admin, Alter or DBO), potentially error log recycles (not sure here perhaps setupadmin, definitely SA), etc.

For maintenance, it is a process you own, you setup and you trust. Like a monitoring tool, I see no issue with maintenance running as sysadmin.

I would also suggest you check out Ola Hallengren's maintenance solution. Maintenance plans are okay and serve a purpose, but he's put a lot of work into his maintenance solution and it works well.

On:

What does guarantee that non-sysadmin role user as owner of a job would have sufficient permissions to run an SQL Server Agent job successfully?

I believe what the documentation author is getting at there is likely the problem your first question deals with. If you make "Domain\JoeBlow" the owner of the job, and that user is just has the correct least privilege for, say, an accounting user, then that login would certainly not have sufficient permissions to do what the job needs to do - the job fails in that case.