Help ! Dangerous user with SA permissions

Hi guys,

I have been told that only users with SA priviledges could check the
status of a job. For this reason, I had to give SA priviledges to this
user so he could check the results of a job (succesful or not) and do
his work. Now, he makes changes on the database without telling me and
last time we nearly lost one day of work as he changed the db option
to truncate on checkpoint. I don't want to be the one to log on and
verify the results of the job everyday so is there any way that I can
give him limited permissions so he could only check the job and leave
the database alone ? I am pregnant so I am afraid that next time he
does something like that I may hit him on the head with the keyboard
(hormones... :-)Any suggestions would help,
Thanks !!!1) Change the sa password immeditaely.

2) Change the job so that it emails you and the target user every time it
completes and/or fails.|||And give him a written warning if you can't sack him immediately.

dcmfan@.aol.comSPNOAM (DCM Fan) wrote in message news:<20040301150956.18347.00000503@.mb-m06.aol.com>...
> 1) Change the sa password immeditaely.
> 2) Change the job so that it emails you and the target user every time it
> completes and/or fails.|||2) Or grant him permissions only to execute sp_help_jobhistory.

"DCM Fan" <dcmfan@.aol.comSPNOAM> wrote in message
news:20040301150956.18347.00000503@.mb-m06.aol.com...
> 1) Change the sa password immeditaely.
> 2) Change the job so that it emails you and the target user every time it
> completes and/or fails.|||pramos00@.bellsouth.net (Patricia) wrote in message news:<b50e122b.0403011202.463c7c0e@.posting.google.com>...
> Hi guys,
> I have been told that only users with SA priviledges could check the
> status of a job. For this reason, I had to give SA priviledges to this
> user so he could check the results of a job (succesful or not) and do
> his work. Now, he makes changes on the database without telling me and
> last time we nearly lost one day of work as he changed the db option
> to truncate on checkpoint. I don't want to be the one to log on and
> verify the results of the job everyday so is there any way that I can
> give him limited permissions so he could only check the job and leave
> the database alone ? I am pregnant so I am afraid that next time he
> does something like that I may hit him on the head with the keyboard
> (hormones... :-)Any suggestions would help,
> Thanks !!!

Create a view that points to the job tables in msdb and give the user
SELECT permissions to this view. If the view is create with DBO the
user should be able to access the information without having SA
priviledges.

Something like:

IF OBJECT_ID('vcheckjobstatus') IS NOT NULL
DROP VIEW vcheckjobstatus
GO

CREATE VIEW vcheckjobstatus
AS
SELECT sj.name, sh.run_status, MAX(run_date) as MAXDATE, MAX(run_time)
as MAXTIME
FROM MSDB.DBO.sysjobs sj
INNER JOIN MSDB.DBO.sysjobhistory sh
ON sj.job_id = sh.job_id
GROUP BY sj.name, sh.run_status
GO

GRANT SELECT ON vcheckjobstatus TO user

Randy
www.Database-Security.Info