September 17, 2012 at 4:55 pm
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?
September 17, 2012 at 5:28 pm
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.
September 19, 2012 at 3:28 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply