通过

Permissions for SQL Server Agent Proxy for SSIS Maintenance Plans

  • 项目

The DM6140 requirement in the SRR Checklist SQL Server v8R1 for the Database STIG can be a little troublesome if you haven't dealt with it before.

Typically, by the time you get around to this requirement, you've already removed all unnecessary permissions. A common problem in this scenario is that you create a Credential, then a Proxy for maintenance plans, then try to run a maintenance plan job, and it fails with a variety of errors. The most common reason for the failures is insufficient permissions, but some of the error messages might not sound like it. Some frequently occuring errors include the following phrases: "could not load package", C0014062, and "OLE DB error code 0x80004005", but most of them will also cite something about a login failure.

So, I've attached a short script to this post that will help you assign the minimum permissions for a SQL Server Agent proxy intended to run maintenance plans. The objects that need permissions set are sp_maintplan_open_logentry, sysmaintplan_log, sp_maintplan_update_log, sysmaintplan_logdetail, sysmaintplan_plan, and sysmaintplan_subplans, and the database roles needed are db_dtsltduser and db_dtsoperator.

When troubleshooting maintenance plan jobs that fail to run under a proxy, you can assign the Windows account the sysadmin role. If the job successfully executes with sysadmin permissions, you can be certain that the problem is insufficient permissions. Remember you must remove the sysadmin permissions or it won't be compliant with DM6140's requirements for least permissions.

By the way, maintenance plans are the most common use of proxies, since you can't use a proxy with T-SQL jobs.

UPDATE: A coworker and I just noticed an oddity on this topic. On SQL Server 2005 SP3, 64-bit, when we changed SSIS job steps to execute under an Agent proxy (which we had already ensured had the correct permissions) and then tried to execute them, they failed. We then determined that all we had to do to get them to work successfully was to edit the maintenance plan package and save it. We can even revert to the previous setting and it still works. So it appears that re-saving the SSIS package (after assigning the proxy account to the job step) forces something necessary to be saved that doesn't get saved just by saving the change in the job step execution configuration.

 

Add proxy permissions for maintenance plans.sql

Comments

  • Anonymous
    September 26, 2011
    I know this blog post is some 18 months old, but it was invaluable in helping me set up the proxy account  permissions for our database backup maintenance plans, so thanks! After some troubleshooting, I did have to grant one additional permission which you haven't listed above and which is not in your script:  sp_maintplan_close_logentry We are using SQL 2008 R2.

  • Anonymous
    January 24, 2014
    The comment has been removed