In the past to solve this I've created a web based app to allow users to change jobs. The app connected via a sysadmin login, but only allowed the user to edit/start/stop the jobs that they had access to via the app.
Another option would be to go the single login route, but have auditing enabled for that login specifically and make sure that the auditing tracks the computer name as well as the login. That way if someone does something stupid you've got an audit trail and it'll tie back to their computer.
Group's can't be used to own jobs as the job runs under the context of the job owner.
There does need to be a better security model for jobs however.
I'd say the easiest possible way to do this is through PowerShell and SMO. Take the following code for instance:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
Out-Null
$SqlServerNames = "Server1", "Server2"
$SqlJobName = "YourJob"
foreach ($SqlServerName in $SqlServerNames) {
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)
$SqlServer.JobServer.Jobs[$SqlJobName].Start()
}
All this does it loop through a list of servers (explicitly stated at as string array variable, but can easily be obtained also from a SQL Server database table, a text file, etc.), connects to the current server, and then starts the job with the job named whatever you set the $SqlJobName variable to.
It's also worth noting that the Start() function will not wait for the job to complete before continuing code execution.
As you can see, in very few lines of PowerShell code you are able to accomplish this task. But I'd take it a step further, such as error handling. You don't want this to bomb out and not know what failed, when it failed, and what servers it did/didn't run on.
foreach ($SqlServerName in $SqlServerNames) {
try {
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)
$SqlServer.JobServer.Jobs[$SqlJobName].Start()
}
catch {
# add your error handling code here
## for instance, write the error to a text file
}
}
And, as always with any code, test this out in a non-production environment to ensure that it does what you think it will and should do. Re-work it for your needs/environment.
As per RoKa's comment:
Can this be edited to check for whether the job is currently running, error handling?
Great point, and definitely something to check for (as well as if the job actually exists by checking for a non-null value):
foreach ($SqlServerName in $SqlServerNames) {
try {
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)
$SqlJob = $SqlServer.JobServer.Jobs[$SqlJobName]
if (($SqlJob) -and ($SqlJob.CurrentRunStatus -eq [Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus]::Idle)) {
$SqlJob.Start()
}
else {
# handle/log accordingly if the job doesn't exist or it's not idle
}
}
catch {
# add your error handling code here
## for instance, write the error to a text file
}
}
EDIT: While re-reading your question, I see SQL Server 2000. Are those going to be the target instances? If that is the case, and you do decide to go with the PowerShell/SMO answer I provided, then definitely definitely definitely test and ensure it is going to do what you want. Test this out very far from production and be 100% sure it does what you think it will.
EDIT: It looks like SMO versions before SQL Server 2012 (i.e. 2005, 2008, and 2008 R2) support managing SQL Server 2000 instances. I'm finding this information through this BOL reference.
Best Answer
Based on the information in SQL Server Agent Fixed Database Roles, it appears that non-sysadmin users must be a member of one of the following fixed database roles in msdb to utilize the SQL Agent:
Each of the above fixed database roles progressively allows more authority than the previous one, but with regards to being able to modify jobs, all three indicate that permissions are granted for owned jobs only.
The way I interpret the information in this post is, if you are not a member of the sysadmin fixed role, you only have the permissions to modify jobs that you own, whether that be an individual or a windows group.
You should be able to easily test this theory by creating two or more users, assigning them to one of the above fixed database roles (making sure they are not members of sysadmin), creating individual jobs under each user and then attempting to alter jobs not owned by that user.
I've included some information below about the permissions for each of fixed database roles mentioned above (highlighting mine).
SQLAgentUserRole Permissions
SQLAgentReaderRole Permissions
SQLAgentOperatorRole Permissions