account to run sql agent jobs

Question

account to run sql agent jobs

sqlfriend

SSC Guru

Points: 52781
More actions
April 29, 2011 at 10:04 am

#238694

Just to a new working place.
I found in some our SQL 2008 servers, we use SQL server agent service account to run most of sql agent jobs.
In SQL 2008 server, the service account is no longer a sysadmin by default(I have verified this by another thread), instead the service itself is a sysadmin for it tied to SID.
So I see current our setup is to manually add the service account to seurity- login, and grant it sysadmin, and use it as job owner of our sql jobs, is it a good practice to set it up?
Thanks

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'm not clear on your question.

Are you asking if the service account should be a sysadmin? If so, no, it shouldn't. That allows rights escalation attacks by anyone who can create an agent job.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

It needn't be a Sysadmin. It may cause security issues as GSquared mentioned. Just make sure that it's a domain account and it has the necessary OS level privileges.

Thank You,

Best Regards,

SQLBuddy.

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

Thanks all.

In our case, most of the job owners are the ssis package author themselves, they are not sysadmin, so the job running under their account. And once they left the company, the job will be broken. So I setup a proxy account using sql service agent account, and in their job they will change it run under that proxy account.

And the service account itself is not a sysadmin, I just add the permissions those jobs need to the account.

Does this sound right? Thanks

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

Granting only the required privileges to the Service account is a good practice.

But making SSIS Package creators as the Job owners is not a good practice bcs the jobs fail once the SSIS Package creators leave the company. So make the Job owners as Sa instead.

Thank You,

Best Regards,

SQLBuddy

forsqlserver SSCoach Points: 18902 More actions · Answer 5

Dear SQLBUDDY I have also same query...But u r saying two cross things...Please clear it...

But making SSIS Package creators as the Job owners is not a good practice bcs the jobs fail once the SSIS Package creators leave the company. So make the Job owners as Sa instead.

It needn't be a Sysadmin. It may cause security issues as GSquared mentioned. Just make sure that it's a domain account and it has the necessary OS level privileges.

Thanks

DBA328 Hall of Fame Points: 3952 More actions · Answer 6

As i know, MS suggest the SQL Server agent service should run as a member of sysadmin fixed server roles. But i made some testing, even without sysadmin right, the agent still work. But i would suggest add them as sysadmin role to avoid potential issue.

http://msdn.microsoft.com/en-us/library/ms191543.aspx

However, for security reason, i would suggest to use SQL Server Agent proxy instead of SQL Agent servcie account to run job ssteps.

http://msdn.microsoft.com/en-us/library/ms189064.aspx