2

Follow up to my question "What's the owner of SqlServerAgent job for?"

Basically, I am trying to understand how to create a user-login with least privileges for CRUD-maintaining SQLServerAgent jobs based on SQL Server Maintenance plans.

I've just checked on SQL Server 2008 R2 that SQLAgentOperatorRole, which is the most privileged role among SQL Server Agent Fixed Database Roles, doesn't have any access (even to view):

  • the Management\Maintenance Plans
    • (as well as) to
      • Management\Data Collection,
      • Management\Resource Governor)

which makes it quiet useless for maintaining SQLServerAgent jobs baased on maintenance plans (my case) ...

So, how to create a user CRUD-maintaining and running SQLServer Agent jobs as well as CRUD-managing Maintenance Plans?

Also, I'd like to better understand the phrase from msdn "Give Others Ownership of a Job":

Assigning a job to another login does not guarantee that the new owner has sufficient permission to run the job successfully

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?

Community
  • 1
Fulproof
  • 1,392
  • 2
  • 26
  • 36

2 Answers2

2

With regards to maintenance plans there is no CRUD implementation. As stated in BOL here:

To create or manage Maintenance Plans, you must be a member of the sysadmin fixed server role.

You might be able to pull off setting up granular control by granting access to the stored procedures used to create maintenance plans (at the bottom of the article I linked above):

sp_add_job

sp_add_jobstep

sp_add_schedule

sp_attach_schedule

I would rethink the business need for this before going to that level. You have to understand that the objects used and permissions required for maintenance plans could change between major version, or even service packs and cumulative updates.

If there is a dire business need for this, I would likely create a dedicated account strictly for this and not grant the day-to-day user account the permissions. Then hope I was supporting Enterprise Editions only and use SQL Server Audit to track the usage of that account.

Though you also have to entrust the level of permissions to the individual you are providing it to, whether that means a training lesson prior to giving the account information away or even having your HR department come up with a security policy for privileged access. If I am not comfortable giving privileged access to someone you can be assured it will be voiced (by email) to management, letting them provide the finial decision (by email). I would then save off that little email as documentation on why I provided the access (for auditing purposes).

1

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.

Mike Walsh
  • 18,278
  • 6
  • 50
  • 74