December 2, 2009 at 12:08 pm
Hi,
I'm tryng to configure SQL Server Agent to be our main scheduling tool in our environment where SQL Sever is present on our severs.
My initial thought was to create a proxy user and a credential for each application owner requesting scheduled jobs on their database and give the application team a shared ID to view and execute jobs manually if they should fail. Only problem is, the lowest MSDB role available to give a user also gives them permission to create and delete jobs they own. Which is more access then I'd like to give.
We would like jobs to be created and scheduled by only DBA's and our Systems Management group.
Is there a way I can re-create the role under a different name and simply deny them access to sp_add_job? Or am I going about this completely in the wrong direction?
please advise,
Thanks
December 2, 2009 at 4:35 pm
Something that works for some environments is to have a table where they have write permission. This table would hold job data. In this table they can submit a job that needs to be rerun. An agent job would be scheduled to run at a regular interval that checks this table and executes any jobs that need to run. Once completed, the status in the table would be updated with the outcome of the job.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 3, 2009 at 5:51 am
Humm... yeah I read that somewhere on the net during my initial investigation of my issue... I did thnk of another solution of adding a role to the MSDB database, I called it deny create and delete. In that role I simply specified deny on the sp_add_job and sp_delete_job and assign those users the both roles (SQLAgentUser and Deny create and delete) and since "deny" will always over rule, this seems to be working as well... I'm also thinking about doing adding and deleting steps to the deny list.
can you think of any reasons why this would not be a good approach?
Thanks,
December 3, 2009 at 9:48 am
As long as it is working, and the users are not overpermissioned - no reason that I can think of would make a "bad" approach.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 3, 2009 at 10:22 am
I hope, here's what I did...
- I got domain user ID created by our security department. It only has domain user rights.
- Created a credential and used that domain user for it.
- Assigned SQL Server permissions to that domain user (db_backup_operator, I'm testing using a backup job that backs up all database, and also datareader/writer... I don't know, may not be required for the backup job).
- Created a sql server user app_jobrunner and assigned it SQLAgentUser and the deny role I created in MSDB.
- Created an SSIS proxy for the app using the credential created above, and assigned principals to the MSDB role SQLAgentOperator and app_jobrunner.
- Created the SQL Server Agent SSIS backup job (using a user that has been assigned the SQLAgentOperator role, we have our own job scheduling group). Selected the job to run as the proxy.
- Changed ownership of the job created to app_jobrunner (with my DBA account, since SQLAgentOperators cant)
The backup job runs successfully for the database that the credential has access to, and fails on all other database, which was my intention...
The reason why I created app_jobrunner was so that if anyone from the application support side needs to run a job for the app they support, they would be able to use a common ID. I was hoping to be able to use a domain group with their ID's in it. But domain groups can't own jobs apparently, and since I want to limit the jobs that an application group can see and execute, the SQLAgentUser role needed to be used... I'm considering elminitating that step and assign the app_jobrunner SQLAgentReader instead...
This sounds like a lot of administration work... but it works... from what I can see, I think more testing needs to be done before finalizing my process. This will have to be done for every app that owns a database and needs to execute jobs and check their status's
Thanks a lot for your comments, please feel free to comment/criticize this method.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply