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.
Author |
Topic |
Edmund
Starting Member
4 Posts |
Posted - 2003-01-02 : 10:21:40
|
HelloI 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 61The specified @job_name ('My Job X') does not exist.Please help me. Please, please, PLEASEMany thanksEdmund |
|
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.sysjobsWHERE (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 |
|
|
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. |
|
|
|
|
|
|
|