Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks. I understand that as soon as a user has access to backups, there is no point left in restricting him because he already would have full access to "protected" data (being able to backup and restore backups where he would want). Though, I need to be 100% sure and explain to others since it is what I was requested from company management. My task is not only backups but also monitoring and tuning SQL Servers... with least possible privileges. I hardly see how it can be done without highest domain and SQL Server privileges –  Fulproof Nov 17 '13 at 16:31
    
I just mean you need additional permissions to run the maintenance. I'd probably look at the Ola script and see what permissions you can get away with. backup database, alter permissions or DDL Admin for index rebuilds/stats updates, checkDBs, and you'll be able to get to the exact perms necessary. And then using Ola's script you can just run that as a SQL Agent job and never have to mess with maint plans :-) –  Mike Walsh Nov 17 '13 at 16:38
add comment

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).

share|improve this answer
    
Just +1'd - I say this is a case of graduating from maint plans ;) –  Mike Walsh Nov 17 '13 at 22:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.