April 1, 2011 at 8:45 am
Just had this on my shiny new SQL 2008 R2 box, with the very first job I have set up (a backup of the sysem databases)!
All the case settings 'look' correct, so I have changed the job to use "sa" for now - I'll set up another SQL account next week to replace this, but may stay away from domain logins until I understand what the problem is.
April 1, 2011 at 9:34 am
Jobs are a pain and even with my super admin AD account sometimes the jobs failed, sometimes they didn't. Depended on if I was still logged onto the system.
Solution is to change job owner to the SQL Agent owner account. If this account is domain\specialsqlsomething, make it the owner of the jobs.
To identify owners of the jobs we change the notifications of failures ect to be the Human owner of the job.
Advantage is humans can loose their jobs and have the AD accounts disabled, but the service accounts survive.
We are on 9.0.4053 versions.
April 1, 2011 at 9:59 am
I use sa for the owner, and heard Brent Ozar last week recommend the same thing. It's consistent as an owner and it doesn't matter for security.
July 4, 2011 at 5:33 am
Unfortunately this has become a big problem for me. Making the SA or any SQL Login as a owner for a job is not always ideal I guess. As a DBA I have to provide sysadmin rights to users to run the jobs in this case since you will have to have sysadmin rights to run others owned jobs.
Any help much appreciated !!!
August 25, 2011 at 9:59 am
Using SA and db_owner really makes the life easy but that power should be maintained by the dba, it will be a bit of a work but i will suggest adding your user to the OperatorRole in the msdb. try to test using a dummy account if you want so you would know what other permission is really needed.
August 20, 2012 at 6:27 am
This is a bit old, but I believe I could add a little information on the topic. I believe using SA for the job owner is not a security problem because this information is only checked by the SQL Agent service at the start-up of the job. Any subsequent action inside the job will be authenticated by the account used to run the SQL Agent service in case of windows auth, or by the specific SQL account in case of SQL authentication mode (like inside a SSIS package).
I use SA as owner to avoid problems of linking important jobs to any account of the domain.
------
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply