June 26, 2007 at 12:53 pm
Hello All,
I hope this is a simple one to answer.
One of my ADBA's had a scheduled job in SQL 2000 server that repeatedly failed. When I looked at the job history and expanded the history view to show the step details, I noticed that the job was being executed as a user that didn't have rights to the database to perform the task. The ADBA stated that he expected the job to run as SA. Once I granted access to the database for that user, the job successfully completed.
My questions is: how are users assigned to execute a job? Is that something that you pass it?
Thanks in advance,
Ronnie
June 26, 2007 at 10:18 pm
"I hope this is a simple one to answer."
Unfortunately, it is not a simple answer.
Here are some of the rules that apply to SQL Server 2000 jobs. These rules are hard-coded into the system procedures and into the msdb views.
Not included are all of the additional rules that apply to maintenance plans and replication jobs. Note that the SQL Server Agent account must have been granted the sysadmin role or the Agent service will give an error on start-up and will then stop.
Job Creation:
Any login that also has the sysadmin role can create a job, regardless of any other rules.
Any login with msdb database role TargetServer, cannot create a job.
Any login that has access to a SQL Server can create a job, the job steps and the job schedules. Access to the msdb does not need to be granted.
The login that creates the job is the job owner.
Job Ownership:
The login that creates a maintenance plan, is the owner of the maintenance plan jobs.
Only a login with the sysadmin role can change the job owner.
If a login owns a job, the login cannot be deleted.
Job Modification and Deletion:
The owner of a job can change or delete a job.
A login with sysadmin role can change or delete any job.
Viewing Jobs:
A login with sysadmin role can view any job
A login with msdb database role TargetServer, can view any job.
A login without the sysadmin role and without the msdb database role TargetServer, can only view jobs owned by the login.
Impact: when one developer creates a job, another developer cannot see the job.
Starting a job:
A login with TargetServer role cannot start any job, regardless of the job owner.
A login can start any job that they own.
A login that has sysadmin rights can start any job, regardless of owner.
The SQL Server Agent can start any job based on the schedule, regardless of owner.
Note that the job will not start when there is an attempt to start a job by a login that does not have sysadmin role and the login is not the job owner.
Job Credentials - what login connects to SQL Server:
When the job owner is a Windows login that no longer exists in Active Directory or the Windows account is locked-out, the job fails.
When the job owner is "sa", the job connects with the Agent's login.
When the job owner is a login that does not have sysadmin role, the job connects as the job owner.
When the job owner is a login that does have sysadmin role, the job connects with the Agent's login.
SQL = Scarcely Qualifies as a Language
June 26, 2007 at 11:09 pm
The job when scheduled executes under the context of the account under which sql server agent service is running. If you have specified explicitly anyother user then it uses the context of the user specified.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 27, 2007 at 6:34 am
Carl Federl, Thank you Thank you!
I have never seen anyone give a way to allow logins to view the jobs they don't own UNLESS they were an SA. Your statement that 'A login with msdb database role TargetServer, can view any job.' was a great eye oener. How have I missed this tidbit?
Yes, that's a little over the top, but I am so excited to have this in my bag of trick now. I've done a little more googling on it and am excited now to be able to give the developers access to see how the prod jobs are set up without having access to alter them.
June 27, 2007 at 7:40 am
GOOD Information!!!
Sorry it wasn't a simple anwser, but this has really helped me understand.
Thanks you all!
Ronnie
June 27, 2007 at 7:43 am
Oh and by the way, the user that executed the job is the same user I have assigned to start the SQL agent service. That makes sense. I don't know why I didn't associate the two.
Ronnie
June 27, 2007 at 6:55 pm
'Your statement that 'A login with msdb database role TargetServer, can view any job.' was a great eye oener. How have I missed this tidbit? "
Because it is not documented.
With a bit of hacking of the msdb system stored procedures and view, you can set up various other roles such as to allow developers to view and start jobs owned by other developers but restrict these privileges by various criteria such SA owner, or by category (db maintenance, replication, etc).
If you do so, be warned that you will be violating the terms of the support agreement.
Here is some SQL that will change the job owner to sa.
Add your own where clause based on your environment and then schedule in a job to run on a regular basis.
select 'exec msdb.dbo.sp_update_job @job_id = ''' + cast( sysjobs.job_id as varchar(255 ) )
+ ''' , @owner_login_name = ''sa''' as SQLCmd
into #Jobs
from msdb.dbo.sysjobs
where owner_sid '0x01'
-- AND
exec master.dbo.sp_execresultset @cmd = 'select SQLCmd from #jobs'
SQL = Scarcely Qualifies as a Language
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply