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?
Ability to manage Jobs that they own
All of the SQLAgentUserRole rights
The ability to review multiserver jobs, their configurations and history
All of the SQLAgentReaderRole rights
The ability to review operators, proxies and alerts
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
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.