Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


minimum roles (server/database) should be given to a user to run jobs


minimum roles (server/database) should be given to a user to run jobs

Author
Message
luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 659
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
Vishal Singh
Vishal Singh
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 517
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
Silverfox
Silverfox
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 1161
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
luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 659
With sqlagentuserrole, I can't even see the jobs from any other user Sad
Vishal Singh
Vishal Singh
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 517
luckysql.kinda (9/23/2009)
With sqlagentuserrole, I can't even see the jobs from any other user Sad


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
Silverfox
Silverfox
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 1161
luckysql.kinda (9/23/2009)
With sqlagentuserrole, I can't even see the jobs from any other user Sad


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
luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 659
Yes this user should be able to run only sql jobs (any jobs; not only his own) and nothing else.

-lk
Silverfox
Silverfox
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 1161
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search