Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

minimum roles (server/database) should be given to a user to run jobs Expand / Collapse
Author
Message
Posted Tuesday, September 22, 2009 10:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310, Visits: 649
I need to give authentication/privilege to a user to run a job. I don't trust him. What are the minimum roles (server/database) should be given to a user to run jobs?

-Lk
Post #792359
Posted Tuesday, September 22, 2009 11:12 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 12:30 AM
Points: 353, Visits: 487
He needs to be added to MSDB system database with role 'SQLAgentUserRole'

-Forum Etiquette: How to post Performance Problems

-Forum Etiquette: How to post data/code to get the best help
Post #792365
Posted Wednesday, September 23, 2009 1:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:43 AM
Points: 2,723, Visits: 1,090
luckysql.kinda (9/22/2009)
I need to give authentication/privilege to a user to run a job. I don't trust him. What are the minimum roles (server/database) should be given to a user to run jobs?

-Lk


As suggested SQLAgentUserRole will allow him to run jobs that he owns, and only jobs that he owns. not all jobs.


--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Post #792391
Posted Wednesday, September 23, 2009 3:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310, Visits: 649
With sqlagentuserrole, I can't even see the jobs from any other user
Post #792476
Posted Wednesday, September 23, 2009 3:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 12:30 AM
Points: 353, Visits: 487
luckysql.kinda (9/23/2009)
With sqlagentuserrole, I can't even see the jobs from any other user


also, make him a part of SQLAgentReaderRole.
I missed to mention it earlier.

And what do you mean you are not able to see tha jobs from other users?


-Forum Etiquette: How to post Performance Problems

-Forum Etiquette: How to post data/code to get the best help
Post #792481
Posted Wednesday, September 23, 2009 4:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:43 AM
Points: 2,723, Visits: 1,090
luckysql.kinda (9/23/2009)
With sqlagentuserrole, I can't even see the jobs from any other user


thais is exactly right, that is what the role is for. Are you saying that you want this user to be able to see all sql agent jobs. does he need to run or modify any of these jobs or just see the list.

If you look in BOL for SQLAgentoperatorrole, you can see the permissions for all three job related database roles


--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Post #792486
Posted Wednesday, September 23, 2009 4:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310, Visits: 649
Yes this user should be able to run only sql jobs (any jobs; not only his own) and nothing else.

-lk
Post #792488
Posted Wednesday, September 23, 2009 4:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:43 AM
Points: 2,723, Visits: 1,090
luckysql.kinda (9/23/2009)
Yes this user should be able to run only sql jobs (any jobs; not only his own) and nothing else.

-lk


Could be interesting, you need to give him SQLAgentoperatorRole to allow him to execute any sql jobs on that server, are you also aware that if you give him that role, he will able to also do the following.

Create/modify/delete his own jobs
enable/disable any jobs
view properties on any job
edit any jobs that he owns/creates
start and stop any job
view job history for all jobs
delete job history for all jobs


It is the most priviledged out of all the job roles and implies a lot of trust, you can in theory do it by granting access to certain stored procedures to allow starting the jobs, but that is not that easy and can be a pain to troubleshoot to get exactly right.


--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Post #792506
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse