March 3, 2010 at 8:16 am
Sometimes job are failing with mesage "The owner () of job <Job Name> does not have server access".
So, thinking to change it to SQL Server Login.
What permissions should be assigned to it, which would be enough to run/configure jobs? I hope SQL Agent new role 'SQLAgentOperatorRole' is Ok or else is it better to set 'sa' as Job owner for all jobs?
Please suggest.
With regards,
Ankur
March 3, 2010 at 9:59 am
Currently we use the same account for job ownership as we use to start and run the SQL Service on the Windows Server. Within SQL that account (MyDomain/SVC-SQL) is only in the Public role and I don't have any problems running jobs.
Hope this helps, at least a little bit.
-D
March 3, 2010 at 10:26 am
We used to make the SQL Agent account, which is a domain account, the owner of all jobs and it almost always worked well. However, when the SQL Server can't authenticate the login with the domain controller because of a network problem, the jobs fail with a message similar to yours. Our solution was to change the job owner to SA, which is a local SQL Server account.
We're satisfied with using SA as the job owner because it's a member of sysadmin, just like the SQL Agent account so it doesn't need any extra privileges.
Greg
March 4, 2010 at 6:44 am
Many Thanks To Greg & Locker for your feedback..
I am also planning to use 'sa' as the Job owner for all the scheduled jobs. But in general it is said that it is not a good practice to use 'sa' as Job owner.
Can u please clarify about the advantages of using the sa as job owner, so that i can state the same to user..
With regards,
Ankur
March 4, 2010 at 9:40 am
Like I said, sa has the same privileges as the SQL Agent account so we didn't have to do anything special for permissions.
It's a bad idea to logon as sa for normal admin tasks, but I don't know that there's anything wrong with using it as a job owner. The jobs still run as the SQL Agent account.
Greg
March 5, 2010 at 8:21 am
Hi,
Greg , Thanks again for showing me the path.:-)
I wish to clarify one more thing:
We are having both SQL Server 2k/2k5 in our scope. As far as SS 2000 is concerned we can go for 'sa',
but in case of 2k5, what about using the SQL Server Agent Roles (i.e. SQL Agent User, Reader & Operator) for the same. Would it be a better choice to use these or we can go for 'sa' in SS2005 as well.
Please shed some light on this as well so that i can go ahead.
With regards,
Ankur
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply