Security for SQLAgent Jobs

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Security for SQLAgent Jobs

Security for SQLAgent Jobs

Rate Topic

Display Mode

Topic Options

Author

Message

dan-572483

Posted Monday, September 17, 2012 4:55 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 330, Visits: 946

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?

Post #1360496

SQLmountain

Posted Monday, September 17, 2012 5:28 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, April 29, 2013 6:28 AM
Points: 436, Visits: 712

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.

Post #1360501

opc.three

Posted Wednesday, September 19, 2012 3:28 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 6,703, Visits: 11,732

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1361657

« Prev Topic | Next Topic »

Permissions