Sql-server – How to give the least permissions for CRUD-managing and running SQLServerAgent jobs as well as Maintenance Plans

The Manage Connections dialog is for a connection to the Database or Instance you are performing maintenance on.

So if you type in a sql authenticated user called 'bob' there, the user bob will be the one that connects to the instance to do the maintenance tasks you are specifying.

You can prove that out by running SQL Server Trace/Profiler (in a dev environment) and seeing the connection being made.

That is basically the user that is connecting to the objects within SQL Server.

The connection to the folders and external resources will be, in this case, whatever your SQL Server Service Account is running as.

It's a bit confusing but it sort of works like this:

1.) Your SQL Server Agent Service Account (or the proxy account used) will do all of the executing. It will call out to the maintenance plan, it will handle the job logging for job history/etc.

2.) When the maintenance plan is run - any of the tasks inside of it will either use whatever permissions the agent account has, or whatever you have explicitly defined in the maintenance plan connection manager -as you have done.

3.) For any access to resources outside of SQL Server - the SQL Server Service account will actually be the account interacting. So to write a backup to a folder, your SQL Server Service account will need that permission. The agent tells the server to run the maintenance plan which tells the server to do a backup. The service account of the SQL Server service then performs what it was requested and it writes out the backup.

Also

You didn't ask here - but I'll mention it again like I did in the last answer. I don't get anything for recommending a free resource - but the Ola Hallengren maintenance solution I mentioned on a related question from you is a really good resource. It will intelligently do your index rebuilds/reorganizes so you only touch what needs to be, it will add more control and intelligence to your statistics updates, it does a great job with backups, etc. But the nicest thing going for it? It gets rid of the black box of maintenance plans and takes a variable out of play for you to have to worry about seeing inside of with some of the more advanced security questions you are trying to get answered.

I would work on educating your security team about jobs...they are server/instance level objects, not database level objects, so db_owner seems severely limited.

Ola recommends sysadmin for the job owners:

Which permissions are needed for the SQL Server Maintenance Solution to work?

If you are using SQL Server Agent, the jobs run under the SQL Server Agent service account that is a member of the sysadmin server role. If you are using a proxy account, I recommend that the account be a member of the sysadmin server role.

If you are using another scheduler, I recommend that the scheduler run under an account that is a member of the sysadmin server role.

If you need to have users execute the stored procedures ad hoc against specific databases, these permissions are needed:

DatabaseBackup: sysadmin

DatabaseIntegrityCheck: EXECUTE on dbo.DatabaseIntegrityCheck, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

IndexOptimize: EXECUTE on dbo.IndexOptimize, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

Regarding the comment about the msdb filling up if you run against a table, when you install his jobs using his big script MaintenanceSolution.sql he installs a CommandLog Cleanup job that will clear out the tables used to log his job steps.