09 July,2010 by Jack Vamvas
During a security audit on SQL server 2005 server, I discovered an administrator had assigned the permission "sysadmin" to a logon.
He explained to me the reason : when they were on SQL Server 2000 it was required to allow a logon read\execute rights on SQL Agent Jobs.
We've now changed this policy to use the SQL Server Agent fixed roles - which allows a more detailed role assignement to logons.
It's worth noting these are mdsb database fixed roles
The choices are:
2) SQLAgentReaderRole (includes SQLAgentUserRole)
3) SQLAgentOperatorRole (includes SQLAgentUserRole and SQLAgentReaderRole)
We've implemented by setting up a separate AD group , adding relevant Windows users - and then adding the logon to the server. That way we can maintain a tighter control on who can view\execute SQL Server Agent Jobs