Allowing your App team to run SQL Agent Jobs

Problem : In most of your projects, there is always that situation that the application team have that need to run their own application jobs in the SQL Agent. But how do you allow them to let them have that right (and fun) without letting them make any changes to the SQL Agent jobs?

Solution : DBA can allow this by granting them certain permissions and making them members of any 3 SQL Agent database roles in the system database msdb.

SQL Agent fixed database roles

There are 3 SQL Agent fixed database roles in msdb namely;

  • SQLAgentOperatorRole – Of all the 3 fixed database roles, this is the most privileged role. Members of this role can manage and run jobs that are owned by them or owned by other accounts. They also have access and enumerate proxies and alerts and delete job history. They automatically inherit the privileges of SQLAgentReaderRole and SQLAgentUserRole.
  • SQLAgentReaderRole – Members of this role can list all multi server jobs, their properties and their history. and not those jobs and schedules that they own. Members of this role automatically inherit the permissions for SQLAgentUserRole.
  • SQLAgentUserRole – Members of this role has the least privilege of the 3 fixed roles. They are allowed to view local jobs, operators and schedules. They only have access to local jobs that they own.

Aside from making the App team members of any of the 3 fixed database roles in msdb database, you will need to grant them permission to execute in some Agent extended stored procedures.

To check if the Agent extended procedures are enabled in your system,

select value, value_in_use from sys.configurations where name = ‘Agent XPs’

The Agent extended procedures that I needed to grant them viewing of the Agent jobs and schedules under SQL Agent menu in SSMS are the following.

use master
go
grant select on master.dbo.sysperfinfo to [YourAppTeamGroup]
go
grant execute on master.dbo.xp_sqlagent_notify to [YourAppTeamGroup]
go
grant execute on master.dbo.xp_sqlagent_enum_jobs to [YourAppTeamGroup]
go
grant execute on master.dbo.xp_sqlagent_param to [YourAppTeamGroup]
go
grant execute on master.dbo.xp_sqlagent_is_starting to [YourAppTeamGroup]
go
grant execute on master.dbo.xp_instance_regenumvalues to [YourAppTeamGroup]
go

After executing the above statement, your App team should be able to see the SQL Agent GUI, view their jobs and job properties, but not make any changes in them.

Now, you have less complains and you made the App team happy!

 

Advertisements

One response to “Allowing your App team to run SQL Agent Jobs

  • Johnb722

    Its like you read my mind! You seem to know so much about this, like you wrote the book in it or something. I think that you could do with a few pics to drive the message home a little bit, but other than that, this is great blog. An excellent read. I will certainly be back. eecdbdfbkbbf

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

SHE GOES GLOBAL

Travel the world, change your life.

My Time to Travel

The travels of an old(er), solo, woman

Blog Home for MSSQLDUDE

The life of a data geek

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman.net/

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: