Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Job HELL - Please help

Author  Topic 

Edmund
Starting Member

4 Posts

Posted - 2003-01-02 : 10:21:40

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


Edmund
Starting Member

4 Posts

Posted - 2003-01-02 : 12:25:07

I recieved the following response from the microsoft news server:

==========================================

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


Go to Top of Page

MuffinMan
Posting Yak Master

107 Posts

Posted - 2003-01-02 : 12:52:57
The easiest way around this permissions problem is to create a staging table that holds the name of the group that requested a job and which job to run.

Create a reoccuring job that periodically (every 10 minutes) checks for records in the staging table. When records exist, start the job requested by the user group. Use an account that has sql administrator rights to start the jobs.

Go to Top of Page
   

- Advertisement -