SQL jobs with securityadmin permission

Question

SQL jobs with securityadmin permission

smitty-1088185

SSCrazy

Points: 2321
More actions
June 15, 2010 at 8:33 pm

#236456

I would like a allow a SQL role the ability to run SQL jobs but I do not want them to be able to create user accounts. How can I accomplish this without giving them securityadmin permissions?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sachnam SSCrazy Points: 2317 More actions · Answer 1

Hi,

So far i could understand your problem . it is related with executed a job using by creating a Database role. You want that the member of that role should only be able to execute the sql jobs .

If i have correctly understood your problem like i stated above.

here is your solution.

grant the SQLAgentUserRole permission to your User defined role.

This role exist in MSDB database so while creating your Role you add your role as a member in MSDB database .

But using this you will only be able to execute local jobs. if you want to execute Multi server jobs as well then pls add the role in 'SQLAgentOperatorRole ' Role under MSDB database itself.

pls. let me know if this has resolved your issue.

smitty-1088185 SSCrazy Points: 2321 More actions · Answer 2

I would like the users to be able to see all SQL jobs but prevent them from creating user accounts.

Even though a add the users to the SQLAgentOperator role they still can't see jobs they do not own. So I have to give the users dbo permission on the Master DB which allows them access to account information.

Thanks in advance.

lakshmi2np Old Hand Points: 373 More actions · Answer 3

This one should help you.

http://msdn.microsoft.com/en-us/library/ms188283.aspx

Thanks.

smitty-1088185 SSCrazy Points: 2321 More actions · Answer 4

smitty-1088185

SSCrazy

Points: 2321

June 19, 2010 at 11:38 am

#1182451

Thank you! That helped a lot

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

smitty-1088185 (6/16/2010)
I would like the users to be able to see all SQL jobs but prevent them from creating user accounts.
Even though a add the users to the SQLAgentOperator role they still can't see jobs they do not own. So I have to give the users dbo permission on the Master DB which allows them access to account information.
Thanks in advance.

You wouldn't need to grant them dbo in master. Just grant them the Operator role in msdb. That will give them the ability to see jobs and execute them. If you must go one step further, you should explore setting up a credential and proxy.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events