Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Security for SQLAgent Jobs Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 4:55 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:50 PM
Points: 550, Visits: 1,611
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
Posted Monday, September 17, 2012 5:28 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 3:27 PM
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.
Post #1360501
Posted Wednesday, September 19, 2012 3:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 7,098, Visits: 12,606
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
Post #1361657
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse