SQL Server agent fixed roles

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:

1) SQLAgentUserRole
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

Read More

How to create a SQL Server Security Audit - SQL Server DBA

Powershell sql server security audit

SQL Server Security Policy

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server agent fixed roles

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer