September 22, 2009 at 10:47 pm
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
September 22, 2009 at 11:12 pm
September 23, 2009 at 1:11 am
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.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 23, 2009 at 3:44 am
With sqlagentuserrole, I can't even see the jobs from any other user 🙁
September 23, 2009 at 3:50 am
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?
September 23, 2009 at 4:04 am
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
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 23, 2009 at 4:08 am
Yes this user should be able to run only sql jobs (any jobs; not only his own) and nothing else.
-lk
September 23, 2009 at 4:35 am
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.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply