WHO ADDS YOUR SQL AGENT JOBS?

A job fails to run. You've verified that the job's owner has server
access. Now what? Be careful who can add SQL Agent jobs and how they are
added. Users may have permissions to add jobs via stored procedures; however,
the jobs may not execute successfully.

You can add jobs as the "guest" user using sp_add_job, sp_add_jobstep,
and sp_add_jobserver while logged in using Windows authentication.
Although the job owner may be your Windows authenticated login, it will fail to
execute. If you discover that a job is failing due to server access
permissions, change the owner of the job.

To do this, go through SQL Enterprise Manager (SEM). When updating SEM
job properties, edit the job step by reselecting Self from the list box
labeled Run As User on the Advanced tab. To avoid these issues in the
future, create the job using SEM or use scripts generated through SEM.

The following script creates a job that impersonates "guest." Run the
job, change the job owner, and rerun the job. (You should be logged in with
a login that has sysadmin privileges.) Check the job history for status.

SETUSER 'guest'
SELECT SUSER_SNAME()
GO

DECLARE @jobID BINARY(16)

EXEC msdb..sp_add_job @job_name = 'test - please delete job'
, @enabled = 1
, @description = 'This is a test job.'
, @job_id = @jobID OUTPUT

EXEC msdb..sp_add_jobstep @job_id = @jobID
, @step_name = 'Step 1'
, @subsystem = 'TSQL'
, @command = 'DECLARE @spid int SELECT @spid =
@@SPID DBCC OUTPUTBUFFER ( @SPID )'

EXEC msdb.dbo.sp_add_jobserver @job_id = @JobID

EXEC msdb.dbo.sp_start_job @job_name = 'test
- please delete job'

WAITFOR DELAY '00:00:03'
GO

SETUSER
SELECT SUSER_SNAME()
GO

DECLARE @NewLoginOwner NVARCHAR(2000)
SELECT @NewLoginOwner = SUSER_SNAME()
EXEC msdb..sp_update_job @job_name = 'test - please
delete job'
, @owner_login_name = @NewLoginOwner

EXEC msdb..sp_help_job @job_name = 'test - please delete job'

EXEC msdb.dbo.sp_start_job @job_name = 'test - please delete job'
GO

----------------------------------------
balajiaptechcah@rediffmail.com or balajitsr@rediffmail.com