SQL Server Agent - Job HELL

[Ed]

Hello

I have users that access a database through a user group. There is a stored
procedure that I have set up to run through a job because it takes quite a
while. The user is notified through SQL Mail.

Now ...

sp_start_job will only run jobs that the user owns for users not in
sysadmin. Therefore I have followed the SQL documentation and used
xp_sqlagent_proxy_account to set up the domain administrator, who is a
member of sysadmin, as the SQL Server Agent proxy account. Then I set the
job's owner as the domain administrator.

According to the SQL documentation users who are not members of sysadmin
will default to using the account set up with xp_sqlagent_proxy_account.
Since the domain administrator is a member of sysadmin and has been set as
the job's owner one would think that there would be no problem getting the
job running with sp_start_job for thoes users who are not in sysadmin.

However I get the following when users try to run the job from the
applicaton:

Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 61
The specified @job_name ('My Job X') does not exist.

Please help me. Please, please, PLEASE

Many thanks

Edmund

[Stefan Delmarco]

Hi Edmund, xp_sqlagent_proxy_account only applies once the job is running.
As per BOL:

"When the user is not a member of the sysadmin fixed server role,
sp_start_job will impersonate the SQL Server Agent proxy account, which is
specified using xp_sqlagent_proxy_account."

It does not affect a user's ability to start a job. The following still
holds true irrespective of xp_sqlagent_proxy_account (as per BOL):

"A user who is not a member of the sysadmin role can use sp_start_job to
start only the jobs he/she owns."

No way to get around this I'm afraid. Only sysadmin's and / or job owners
can start jobs (irrespective of the ownership chain). The error "The
specified @job_name ('My Job X') does not exist." is caused by
sp_verify_job_identifiers not finding any rows in the sysjobs_view view in
MSDB (which is hardcoded to prevent non-sysadmin and non-job owners from
starting jobs):

SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)

Instead of having the user start the job directly you could set a flag in a
table. Then you could set the job to execute periodically but then modify
the job to first check the flag before performing any actual work. If the
flag is set then do the work (and reset the flag). If the flag is not set
then do nothing.

Cheers,
Stefan Delmarco

"Ed" <edmundr...@hotmail.com> wrote in message
news:O3IJnNnsCHA.2304@TK2MSFTNGP12...

[Ed]

Thank you Stefan.

I will use your idea and schedual the job, executing the procedure based on
the param table. The way it is explained in the SQL Documentation one would
never guess that it applied only to jobs currently running.

Again, Thank you

Edmund

"Stefan Delmarco" <Stefan_...@hotmail.com> wrote in message
news:3e146f90$1...@news.microsoft.com...

[Jobi]

I've implemented an other way of dynamic job-submission for non job-owners.
I provided a workaround using alerts and messages that start jobs.
The downside of this system is that is has to use nt-eventlog-logged alerts.
Also a user has to know the right error-nr to raise.

Here is how it's implemented :

USE Master
go

Declare @MessageNummer int
Declare @JobNaam varchar(128)
Declare @DbNaam varchar(128)

set @MessageNummer = 62004 -- must be > 50000
set @JobNaam = 'p_job_Sync' --
set @DbNaam = 'BitsAndPieces' --

Declare @wrkstr1 varchar(128)
Declare @wrkstr2 varchar(128)

set @wrkstr1 = 'Requesting Job ' + @JobNaam + ' to be launched.'
-- add message
exec sp_addmessage @msgnum = @MessageNummer
, @severity = 10
, @msgtext = @wrkstr1
, @with_log = 'true'

set @wrkstr1 = 'Requesting_Job_' + @JobNaam
set @wrkstr2 = 'Requesting Job ' + @JobNaam + ' to be launched.'

-- Alert definiëren in MSDB
exec msdb.dbo.sp_add_alert @name = @wrkstr1
, @message_id = @MessageNummer
, @severity = 0 -- message_id has been given
, @enabled = 1 -- 0 = disabled / 1 = enabled
, @delay_between_responses = 0
, @notification_message = @wrkstr2
, @database_name = @DbNaam -- restrict so it only reacts when raised
from this database
, @job_name = @JobNaam

exec msdb.dbo.sp_help_alert
go

In this case the user has to raise error 62004 to launch job 'p_job_Sync'
and when he raises the error he has to be connected to DB 'BitsAndPieces'.

Jobi

"Ed" <edmundr...@hotmail.com> wrote in message

news:OwkCQNosCHA.1632@TK2MSFTNGP12...