Learn more about SQL Server tools

   
   























Latest SQL Server Tips



























Free SQL Server Learning

Get your SQL Server in shape this Spring with Spotlight on SQL Server

 

Accidental DBA or DBA We've got You Covered with SQL Diagnostic Manager

Upgrade Strategies for Highly Available SQL Server Environments

Avoiding, Detecting, and Repairing SQL Server Database Corruption



























SQL Server Agent Fixed Database Roles

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (2)   |   Related Tips: More > SQL Server Agent

Problem
One mantra across the industry is the need to fine tune security for all infrastructure components.  In SQL Server 2005, Microsoft responded with an almost overwhelming number of granular security rights which in some respects requires DBAs and Developers to think about SQL Server security in a brand new way.  One key area that we are going to look at today is a few of the new security options for SQL Server Agent.  SQL Server 2000 had some semi granular fixed server roles for the database engine but no roles specifically for SQL Server Agent.  With SQL Server 2005 some of those same server role principals have been applied to the SQL Server Agent giving this portion of the architecture a big step forward.

Solution
SQL Server Agent now has 3 roles which reside in MSDB database offering a new set of opportunities to fine tune access to working with Jobs.  Job management is certainly one area where security improvements are a much welcomed addition.

What are the differences in the SQL Server Agent roles?

ID Role Description
1 SQLAgentUserRole
  • Ability to manage Jobs that they own
2 SQLAgentReaderRole
  • All of the SQLAgentUserRole rights
  • The ability to review multiserver jobs, their configurations and history
3 SQLAgentOperatorRole
  • All of the SQLAgentReaderRole rights
  • The ability to review operators, proxies and alerts
  • Execute, stop or start all local jobs
  • Delete the job history for any local job
  • Enable or disable all local jobs and schedules

Source - SQL Server Agent Fixed Database Roles

What types of users should be granted these rights?

Although production DBAs will have System Administrator rights, the new SQL Server Agent Fixed Database Roles would be considerations for:

  • Junior DBAs
  • Developers
  • Data Warehouse Developers
  • IT Operations
  • Management

How do I grant these rights?

The rights are granted at the MSDB database level.  To grant rights the follow these steps:

  1. Open SQL Server 2005 Management Studio
  2. Navigate to Databases | System Databases | msdb | Security | Roles | Database Roles
  3. Double click on 1 of the roles to add members to the role
  4. Use the Database Role Properties window to manage the users

Are any additional resources available?

Check out these SQL Server 2005 Books Online articles:

Next Steps

  • Take advantage of these new granular security privileges to best secure your Jobs.  There will no longer be an excuse for having to grant a great deal of privileges, these roles have filled that void.
  • The SQL Server Agent roles are just the tip of the iceberg when it comes to SQL Server 2005's granular security model.  With SQL Server 2000, granting security left a bad feeling pit in your stomach, but SQL Server 2005 has squashed those security concerns.
  • As you begin to deploy the SQL Server 2005 platform, question the privileges asked for by users and IT personnel.  Once you have a clear understanding of their needs, not wants, dig into the new security features with SQL Server 2005 to grant the appropriate privileges.


Last Update: 8/16/2006


About the author
MSSQLTips author Jeremy Kadlec
Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as a SQL Server Consultant at Edgewood Solutions, co-founder of MSSQLTips.com as well as Baltimore SSUG co-leader.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, April 08, 2015 - 9:34:36 AM - bernie black Read The Tip

Never had to use these till now.  Perfect how-to. Thanks.


Wednesday, June 27, 2012 - 4:25:03 PM - Josh Read The Tip

Pleae rephrase the warning to resemble the nice content of this website...

 

JC




 
Sponsor Information