January 25, 2007 at 9:58 am
Hi
I am currently testing SQL 2005 before a major deployment, and I have noticed several things in relation to security.
1) How can I make non SA users be able to view the status of SQL a gent jobs, i.e if the job is running etc.
2) Is there a way in which Non SA users can alter jobs without making them the owner of the job.
3) Lastly, Is there anyway I can allow non SA users to run xp_cmdshell
I look forward to hearing from you all.
Thanks
January 25, 2007 at 10:29 am
I use the following query to monitor the status of my nightly jobs....put it into a procedure....
Eric
select
distinct [name] as 'Job Name',
case [enabled] when 1 then 'Enabled' else 'Disabled' end as 'Enabled',
cast (ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':') as datetime) as 'Last Run',
step_id
as Step,
case [h].[run_status] when 0 then 'Failed' else 'Success' end as 'Status' ,
STUFF(STUFF(REPLACE(STR(run_duration,6),' ','0'),5,0,':'),3,0,':') as 'Duration',
cast (ltrim(str(next_run_date))+' '+stuff(stuff(right('000000'+ltrim(str(next_run_time)), 6) , 3, 0, ':'), 6, 0, ':') as datetime) as 'Next Run'
from msdb.dbo.sysjobs j
left join msdb.dbo.sysjobschedules s on j.job_id = s.job_id
join msdb.dbo.sysjobhistory h on j.job_id = h.job_id
where step_id = 0
and h.instance_id in (select max(sh.instance_id)
from msdb.dbo.sysjobs sj
join msdb.dbo.sysjobhistory sh on sj.job_id = sh.job_id
where h.step_id = 0
group by sj.name
January 26, 2007 at 10:28 am
Your questions 2 and 3 amount to "How can I open huge security holes for non-sysadmins?". If I knew an easy way to give alter-any-job and xp_cmdshell rights to non-sa users I probably wouldn't put it online.
One way to let people run specific sa-type tasks is to create an Agent job that performs the task, then create an alert that runs the job in response to an unused error number. Then users can use RAISERROR with that error number to invoke the job.
You can put users in the datareader role in msdb to let them view job settings and history. I did that this week for a new developer that was trying to get a handle on all the jobs scheduled on a production server by the developer she replaced. This query is similar to the one above, but instead of showing history it shows the schedules and the commands for each step. (On SQL 2000 systems, replace the "LEFT JOIN (...) s" subquery with "LEFT JOIN sysjobschedules s".)
use
msdb
go
SELECT j.name, SUSER_SNAME(j.owner_sid) AS owner,
case when s.job_id is null then 'Not scheduled'
WHEN freq_type = 128 THEN 'When idle' else
case [freq_type] when 1 then 'Once'
when 4 then 'Every ' + case when freq_interval = 1 then 'day' ELSE cast(freq_interval as varchar) + ' days' end
when 8 then
rtrim(case when freq_interval & 1 > 0 then 'Sunday ' else '' end
+ case when freq_interval & 2 > 0 then 'Monday ' else '' end
+ case when freq_interval & 4 > 0 then 'Tuesday ' else '' end
+ case when freq_interval & 8 > 0 then 'Wednesday ' else '' end
+ case when freq_interval & 16 > 0 then 'Thursday ' else '' end
+ case when freq_interval & 32 > 0 then 'Friday ' else '' end
+ case when freq_interval & 64 > 0 then 'Saturday ' else '' end)
when 16 then 'Every ' + cast(freq_interval as varchar) + ' day of the month'
when 32 then
case when freq_relative_interval & 1 > 0 then 'First ' else '' end
+ case when freq_relative_interval & 2 > 0 then 'Second ' else '' end
+ case when freq_relative_interval & 4 > 0 then 'Third ' else '' end
+ case when freq_relative_interval & 8 > 0 then 'Fourth ' else '' end
+ case when freq_relative_interval & 16 > 0 then 'Last ' else '' end
+ case freq_interval when 1 then 'Sunday' when 2 then 'Monday' when 3 then 'Tuesday'
when 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday'
when 8 then 'Day' when 9 then 'Weekday' when 10 then 'Weekend day' END + ' of the month'
when 64 then 'When Agent starts' end
+ ' at ' + stuff(stuff(replace(str(active_start_time,6),' ','0'),5,0,':'),3,0,':')
+ case when freq_subday_type = 1 then '' else
' repeat every ' + cast(freq_subday_interval as varchar)
+ case when freq_subday_type = 2 then ' second' when freq_subday_type = 4 then ' minute' when freq_subday_type = 8
hen ' hour' end
+ case when freq_subday_interval > 1 then 's' else '' end
+ case when active_end_time = 235959 then '' else ' until ' + stuff(stuff(replace(str(active_end_time,6),' ','0'),5,0,':'),3,0,':')
end end end as Schedule,
t.database_name, t.database_user_name, t.subsystem, t.command
FROM sysjobsteps AS t
INNER JOIN sysjobs AS j ON t.job_id = j.job_id
LEFT JOIN (
SELECT sx.schedule_id, sx.job_id, sc.name, sc.enabled, sc.freq_type, sc.freq_interval, sc.freq_subday_type, sc.freq_subday_interval,
sc.freq_relative_interval, sc.freq_recurrence_factor, sc.active_start_date, sc.active_end_date, sc.active_start_time, sc.active_end_time,
sx.next_run_date, sx.next_run_time, sc.date_created
FROM sysjobschedules AS sx
INNER JOIN sysschedules AS sc ON sx.schedule_id = sc.schedule_id
) AS s ON j.job_id = s.job_id AND s.enabled = 1
WHERE (j.enabled = 1)
ORDER BY j.name, t.step_id
January 26, 2007 at 11:55 am
Take a look New Roles in the msdb Database
SQL Server Agent in SQL Server 2005 adds three new roles to the msdb database:
| • | SQLAgentUserRole Users added to the SQLAgentUserRole role will have the same SQL Server Agent experience as they had in SQL Server 2000. These users can create jobs and manage only jobs that they created. |
| • | SQLAgentReaderRole Users added to the SQLAgentReaderRole role will have the same privledges as those in the SQLAgentUserRole with the addition of the ability to enumerate and view the history of all jobs.. |
| • | SQLAgentOperatorRole Users added to the SQLAgentOperator role will have the same privledges as those in the SQLAgentReaderRole with the addition of the ability to execute local jobs that they do not own. |
http://msdn2.microsoft.com/en-us/library/ms188283.aspx
MohammedU
Microsoft SQL Server MVP
January 29, 2007 at 9:36 am
Hi John,
I guess you know the security risks associated with what you're asking (and to which Scott has mentioed too). But, if this is a must, then a technique I have used before is to wrap such calls to stored procs I create. Within these I can audit and control who can fire the job scripts and xp_cmdshell, and of course report to me almost immediately any unauthorised activity.
Hope this is useful.
Regards
SQLBob
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply