Post reply

Question for DBAs - Manage SQL Jobs

  • November 25, 2013 at 12:38 pm

    #280499

    Let me apologies for first If I am posting duplicate topic. :ermm:

    This question goes to all my dear DBAs - How can a person manage a scheduled SQL jobs that created by any user without having SYSADMIN rights? Is there any workaround?

    PROBLEM:

    We are running the healthcare data warehouse with three environments (Prod, Dev and QA) - ALL three environments has same schedule ETL jobs. We do lot of analytic and prediction work. Our DBA took our rights and grant to DB_OWNER for all four developers. We are OK with but when it comes to managing ETL Job - we can't modify job scheduled under "SA" (as it is right now) due to insufficient rights.

    DBA SOLUTION: - Their work around is to schedule a job under individual user -

    OUTCOME: - We tried that but it is so much pain as I am not able to modify my co-work job while he/she is on vacation. I am an on-call this week and my responsibility is to make sure all job run fine regardless environments.

    Our Solution to DBA -

    Option A - To provide a unique account (assume with SYSADMIN rights) with SQL login where we can use only to manage our ETL jobs regardless who created.

    Option B - Give each of my developer rights where they can modify job regardless who created ( I believe it is not doable as it required SYSADMIN rights).

    What I am looking for is a solution, our DBA said that they don't accept either of the option I provided above - Their response is to go and figure out yourself.

    Note: our DBA team is not involved in our Application development, it is not their job responsibility. Their responsibility is to manage the SQL Server - (backup, patch, install, upgrade..etc...)

    Please advise.

    Thanks in advance for your help.

  • November 25, 2013 at 1:52 pm

    #1668966

    this link will probably help enourmously, and keep you from granting sysadmin rights when it's not required:

    Microsoft.com:Configure a User to Create and Manage SQL Server Agent Jobs

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 25, 2013 at 3:00 pm

    #1668992

    keyun21 (11/25/2013)

    Their response is to go and figure out yourself.

    Not exactly "Exceptional DBAs", in my book. :blink: The link that Lowell provided is definitely the place to start.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 26, 2013 at 6:36 am

    #1669122

    The only way I have found to enable a group of people to manage each others jobs without sysadmin is basically as the URL Lowell refers to describes.

    A SQL account must be used by your group for the purpose of managing the jobs as unfortunately a windows group cannot own a job. This SQL account should own all the jobs. You need to grant the rights described in the URL but the full set of rights needed will depend on what the jobs do.

    you will have to work through until you get it right for your environment but I expect you will need these:

    GRANT EXECUTE on master.dbo.xp_sqlagent_enum_jobs

    exec sp_grant_login_to_proxy @login_name = 'your account',@proxy_name = 'your SSIS proxy'

    grant select on msdb.dbo.sysproxies

    grant SELECT on msdb.dbo.sysjobs

    grant select on msdb.dbo.sysjobs_view

    grant select on msdb.dbo.syscategories

    A connect item was raised to microsoft about this issue (it crops up a lot) by they said it was by design.

    ---------------------------------------------------------------------

  • November 26, 2013 at 7:16 am

    #1669139

    You might find what you need by looking into the following roles:

    SQLAgentUserRole

    SQLAgentReaderRole

    SQLAgentOperatorRole

    Check BOL

    Good Luck

  • November 26, 2013 at 7:26 am

    #1669145

    Thanks Everyone for your quick reply.

    I will take your suggestion to my DBA team and see if I can have them to set up.

    I will come back and post the outcome.

    Again, thanks everyone for your kind help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply