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


Security for SQLAgent Jobs


Security for SQLAgent Jobs

Author
Message
dan-572483
dan-572483
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 1958
We have some users who sometimes need to directly edit certain databases. Their protocol sensibley is to back a database before editing it. However, as a result of a recent security audit their sysadmin rights were taken away. While they can still directly edit data in the appropriate DBs using SSMS, they cannot create backups.

The easy answer is to add their AD group login to the db_backupoperator database role on the DBs they work on. But instead of right-clicking the DB they're going to work on and selecting Tasks/Back up, I'd like them to run manaully launch the nightly backup job for that database instead, so that the backup file will be created in the same folder and be deleted on the same schedule as the nightly backups.

Is a user's ability to run a SQLAgent job dependent only on their rights to do what's in the job? Or are other permissions required?
SQLmountain
SQLmountain
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 782
The SQLAgent job is dependent on the privileges of the SQL Server Agent service.

Reading between the lines, you might also be needing to assign the SQLAgentOperatorRole to those user accounts. I am not necessarily recommending this since I do not know the details of your situation, but this is one way of give non-administrators the permission to execute SQL Server Agent jobs.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8275 Visits: 14368
bret.christoe (9/17/2012)
The SQLAgent job is dependent on the privileges of the SQL Server Agent service.

This is true only when the job is owned by a member of the sysadmin Fixed Server Role.

If the job is owned by a non-sysadmin then each job step runs in the security context of the job owner unless the job step is using a proxy account in which case it executes in the context of the proxy account.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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