August 2, 2011 at 12:44 pm
Hi there,
Our developers should have the ability to do whatever they want with jobs in msdb in our DEV environments. Create jobs with SSIS packages, T-sql, schedule, drop, change, etc. They are in development as a Windows AD Group.
Issue:
Unable to add or run jobs.
Error Message:
The EXECUTE permission was denied on the object 'sp_add_job', database 'msdb', schema 'dbo'. (Microsoft SQL Server, Error: 229)
What exists:
Credential has been created.
Proxy has been created.
Proxy assigned to subsystems.
Proxy granted to Windows AD Group.
SQLAgentOperatorRole assigned.
dbo role assigned.
What am I missing? Any help greatly appreciated!
¤ §unshine ¤
August 2, 2011 at 12:47 pm
Give them SA in dev. Read-only max in prod (if any access at all).
Test and QA should be used the application's rights.
August 3, 2011 at 10:34 pm
WE dont want to give them sa because they have the ability to cvhange configurations and passwords to logins. Microsoft created this credential/proxy security. I'm assuming it has to work somehow.
¤ §unshine ¤
August 3, 2011 at 11:25 pm
To grant access to SQL Agent - you are not going to be able to use an AD group. You need to add each individuals login and it is case-sensitive. Meaning, if the AD user is 'domain\MyUser' and you create the login as 'DOMAIN\myuser' it will not work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 4, 2011 at 5:55 am
sunshine-587009 (8/3/2011)
WE dont want to give them sa because they have the ability to cvhange configurations and passwords to logins. Microsoft created this credential/proxy security. I'm assuming it has to work somehow.
My train of thoughts is that I don't want to limit them in testing stuff in dev nor bugging everytime they need something.
If they screw up something they have to fix it themselves... which is a good incentive to be careful.
Just setup main. plans to backup the dbs and let them run wild.
August 4, 2011 at 7:12 am
this is how the sqlagentoperatorrole is designed to work, it still only allows you to fully manipulate jobs you actually own. If this gives you problems (and you are not the only one) please vote for this connect item, there is a need for a sort of sysadmin of sqlagent role
https://connect.microsoft.com/SQLServer/feedback/details/156852/sql2005-sqlagent-msdb-security-roles
I keep seeing this reported as an issue but if no-one votes for a change to it it will never change.
the only workaround other than sa is to set up a SQL account (it has to be a SQL account, not a windows account), give it sqlagentoperatorrole and whatever other rights it requires, and get all the devs who want to manipulate each others jobs to use that account when working with SQLagent jobs.
permissions required are likely to include
dbcreator role
EXECUTE on master.dbo.xp_sqlagent_enum_jobs
'SQLAgentOperatorRole'
exec sp_grant_login_to_proxy @login_name = 'your SQL ID',@proxy_name = your proxy'
select on msdb.dbo.sysproxies
if the job creation SQL contains exists checks
SELECT on msdb.dbo.sysjobs
select on msdb.dbo.sysjobs_view
select on msdb.dbo.syscategories
access to the databases the jobs run against
---------------------------------------------------------------------
August 5, 2011 at 12:38 am
Youn need to grant your developer group membership of the SQLAgentOperatorRole to allow them to create jobs - unless they login to the server as the proxy that you have created.
The proxies are more aligned to having the job steps run with specific credentials which the Developers group may not have.
Note that the jobs will be owned by specific developers, not the Group so only a specific developer will be able to update their job.
September 9, 2011 at 7:32 am
@George-Sorry I've taken so long to reply. Thank you. I did vote. I'll be trying your suggestions.
@Martin- Thank you. I'll be trying your suggestions as well
This is too complicated for running jobs... especially in dev! 🙂
¤ §unshine ¤
September 9, 2011 at 8:07 am
cheers
---------------------------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply