Re: SQL 2000 view current job status best practice

There are a thousand different ways to implement this but here are the
basics.

I would create a stored procedure with something like this...
Create Procedure usp_runningjobs_s
as
begin
SELECT
sysp.cpu ,
sysp.spid,
sysp.status,
sysp.hostname,
sysp.program_name,
sysp.cmd,
sysp.cpu,
sysp.physical_io,
convert(sysname, rtrim(sysp.loginame)) as loginname,
sysp.spid as 'spid_sort',
substring( convert(varchar,sysp.last_batch,111) ,6 ,5 ) + ' ' +
substring( convert(varchar,sysp.last_batch,113) ,13 ,8 ) as
'last_batch_char'
from master.dbo.sysprocesses sysp (nolock)
inner join master.dbo.sysdatabases sysd (nolock)
on sysp.dbid = sysd.dbid
end


Then what you could do is grant permissions to execute the procedure to the
users that you need. Or, even better is create a little .net webpage that
calls this procedure and returns the results to a gridview.

What do you think?



--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/


"Richard Cote" <RichardCote@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F4DCBDD9-7A0F-4F7E-8F21-868AEF7BCCFF@xxxxxxxxxxxxxxxx
We have application support personnel who need to view the current status
of
jobs running on production SQL 2000 (SP4) Servers. The application
support
group has very limited access to these production servers - such that they
do
not have enough permissions to execute these jobs. Therefore, changing
the
job owner is not a viable option. What is the best way to:

- allow the support personnel to view the status of jobs
- not increasing the account permissions within the databases



.

Relevant Pages

  • Re: Giving access to manage sql jobs
    ... Do you want the user to be able to modify only his/her jobs or all people? ... He/She mus be a member of sysadmin server role otherwise you are going to ... > What permissions, and how, can I give them permissions without making them ...
    (microsoft.public.sqlserver.security)
  • Re: permissions to view jobs but not change them
    ... Sorry, I meant view jobs... ... > Tibor Karaszi, SQL Server MVP ... >> is there any combination of permissions or a role that will let a user view ... >> stored procedures and their success without being able to modify them? ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2000 view current job status best practice
    ... Then what you could do is grant permissions to execute the procedure to the ... Warren Brunk - MCITP - SQL 2005, ... jobs running on production SQL 2000 Servers. ...
    (microsoft.public.sqlserver.security)
  • permission needed to run CmdExec job steps
    ... Can someone tell me what permissions are needed for logins to be able ... I recently removed everyone from System Admin and have been working through ... I now have some jobs that fail and here is the message ... CmdExec job steps. ...
    (microsoft.public.sqlserver.security)