March 17, 2005 at 8:29 am
How does one give a user full access to job administration, yet not be a sysadmin?
My situation - I've got a couple of servers where we want one of the developers to maintain the jobs, but not have access to other databases on the server. Problem is that you can't even see the jobs unless you're a sysadmin - OR you're in the TargetServersRole in msdb. Because I would like this user to have more than just read access, what I've done is grant a whole bunch of rights to TargetServersRole (I suppose I could create a 2nd role, but anyway) which lets them do most anything with jobs; edit, start, stop, etc.
The one thing I can't figure out is the "status" column. I've got it where the user can refresh the list of jobs, but the "status" column doesn't get updated. They can see the Last Run Status change when the job completes, but otherwise have no way of telling that the job actually started.
Any ideas?
Jeff
March 18, 2005 at 12:21 am
what you can do is give that user a right to execute sp_start_job stored procedure.
March 18, 2005 at 9:04 am
nah, see, it doesn't help. They're able to start the jobs just fine, but even refreshing the list of jobs doesn't update the "status" field. So they don't know if the job actually started or not.
March 18, 2005 at 11:52 am
I've hit this and similar issues before, and know I'm likely to tackle it again in the near future, so I did some messy research. Following are the notes I made while doing so.
Ultimately, it looks like you'd have to hack the system to get this information for non-SySAdmin users, even to the point of providing your own user interface (as opposed to EM). Alternatively, you could modify the Microsoft-provided system procedures, but by and large that qualifies as a Really Bad Idea (unsupported modifications to the system, compatibility and maintenance issues with subsquent patches and bug fixes, etc. etc. etc.)
On the plus side, "TargetServersRole" doesn't appear anywhere in this. I know I once saw ugly situations where this role was conspicous in its [either presence or absence] in certain critical SPs, such that it didn't provide the flexibility that we wanted to extend to non-SySAdmins.
Good luck!
Philip
- - - - - - - - - - - - - - - - - - - - - - - - -
Start up SQL Profiler
In EM, open up to list all SQL Agent jobs
Profiler shows that two things are done by EM to show all jobs:
exec msdb..sp_help_job
exec msdb..sp_help_category @class = N'JOB'
(note that these are in msdb, not master)
(I didn't look at sp_help_category this time around)
Execute privileges for sp_help_job is assigned to the Public and the TargetServersRole roles.
Execute privileges for sp_help_categoryt is assigned ONLY to the Public role. (This role is intended for master/slave mutli-server job maintenance issues, and not for extending job management to non-SySAdmins. I really, really hope they address this in SQL 2005...)
Analyzing sp_help_job shows:
- This job accepts a whole lot of parameters, none of which are passed in by this "show everything" call.
- sp_verify_job_identifiers
- This just validates job names and IDs
- It calls msdb.dbo.sp_is_sqlagent_starting, which calls master.dbo.xp_sqlagent_is_starting, which as an extended stored procedure lands outside our current sphere of interest.
- If a single job is specified via parameter, one side of an "IF" statement does a whole lot of stuff that I'm passing over for now. (It looks like it returns a lot of detailed information about the requested job.)
- On the ELSE side of that IF, all jobs are worked over
- A whole lot of parameter checking is done--irrelevant here
- It then runs [msdb..]sp_get_composite_job_info and returns whatever it returns.
Analyzing sp_get_composite_job_info:
- Right off, we get a useful comment:
-- By 'composite' we mean a combination of sysjobs and xp_sqlagent_enum_jobs data.
-- This proc should only ever be called by sp_help_job, so we don't verify the
-- parameters (sp_help_job has already done this).
- It determines the caller's id (for job ownership) and whether they've got SysAdmin rights
- Ooh, it then calls master.dbo.xp_sqlagent_enum_jobs, passing in SysAdmin and Owner names. Looks bad... this is an extended procedure, and we can't hack that.
- A bunch of multi-server stuff in here (again, I'm skipping over it)
- Note that there are very useful comments in this procedure. Kudos to the unsung developer who wrote it!
- At the end it returns a set of 3-4 dozen columns. Impossible to tell which EM actually uses, But:
- "current_execution_status" has got to be it.
- This is an INT column.
- It ultimately comes from the extended procedure call.
xp_sqlagent_enum_jobs
- Is not documented in BOL
- Crops up a lot on Google
- (I can't tell the default execution rights on it, as we revoke all XP_ public permissions upon installation of a SQL Server instance.)
So the trick would be to set up a call to this XP, forcing the SysAdmin parameter to always be 1.
- Just how serious a violation of security or licensing agreements this might be, I couldn't say...
- This will return a bunch of info; liberate the code in sp_get_composite_job_info to construct a #Temp table and INSERT... EXECUTE... to populate it.
- Ultimately, column "State" is what you're looking for, and it's an integer value. Deciphering this value into something usable, well, that's where Google, MS KB and Technet, and old fashioned testing/experimentation comes in to play here.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply