May 5, 2006 at 9:06 am
I have a super user who wants to be able to view various jobs scheduled on SQL agent. I want to only grant view capabilites (no updates, deletes, etc.). I created a user as db_datareader, gave access to his database, master and msdb. When I go to view jobs with this login, it tells me "no jobs" (with sa, of course, everything is displayed). I know SQL 2005 allows this but is it possible in SQL 2K SP4?
Terry
May 5, 2006 at 9:20 am
I don't think so; I checked the Fixed Server Roles in BOL and it doesn't appear it's possible 2K. What if you did this:
1. You create a job that reads from MSDB, gathers the information he's looking for, probably stuff like Job History, Start and Stop Times, etc and then writes it to a table on his database.
2. Write him an SP that reads the data in a format that he wants.
Just a thought.
HTH,
Mark
May 5, 2006 at 9:27 am
Terry,
See if you have a role called TargetServersRole in MSDB. It's an undocumented role use for multi-server administration. If it's there, you could add the user to the role. You may need to grant TargetServersRole execute permission to sp_help_job and sp_help_jobhistory.
Greg
Greg
May 5, 2006 at 9:37 am
When looking at the sp_ solution, I notice I only have data in sysjobhistory going back about 3 weeks. I guess if I run the sp_ going forwar to a work table, I'll retain the history. At what point does it purge this table?
Terry
May 5, 2006 at 9:49 am
It's a SQL Server Agent setting. Right-click on the Agent in EM, select properties, Job System tab. You can choose whether or not to limit the job history log size and what the limit will be.
Greg
Greg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply