execute permission on sp_start_job for non-sysadmi

Question

execute permission on sp_start_job for non-sysadmi

kerstin

Old Hand

Points: 302
More actions
September 9, 2002 at 4:36 am

#79696

hello everybody,
for a data-feeding application we have to provide the possibility to start a job on our sql-2k server (the job is starting replication to production server).
the job is owned by another user (say 'job-dba') as the users connecting to the server by the application (these all belong to a specially created role named 'data-feeder').
now - the job start-up is inside a procedure ('start_replication' with execute permissioin to 'data-feeder') which is being called by the application.
we have also set up a proxy account for sql-server-agent for jobs to be executed by users who are not member of sysadmin-role.
even though when the job is being called by anybody not member of sysadmin-role, it fails. i'm now trying to find out what else to do in order to make it work. i have copied the information of BOL here for you to take a quick glance if you like:
-------------------------------------------
'Execute permissions default to the public role in the msdb database. A user who can execute this procedure and is a member of the sysadmin fixed role can start any job. A user who is not a member of the sysadmin role can use sp_start_job to start only the jobs he/she owns.
When sp_start_job is invoked by a user who is a member of the sysadmin fixed server role, sp_start_job will be executed under the security context in which the SQL Server service is running. 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. If the proxy account is not available, sp_start_job will fail.
'
------------------------------------------
any help is greatly appreciated.
thank you in advance,
kerstin

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 1

quote:
the job is owned by another user (say 'job-dba') as the users connecting to the server by the application (these all belong to a specially created role named 'data-feeder').

If I am reading this right, the job belongs to job-dba. However, the user trying to execute the job is NOT job-dba. If I'm interpreting this right, that explains the failure.

In order to successfully execute sp_start_job one of two things must be true:

(1) you have sysadmin server role rights

(2) you own the job

Even though the public role has access to execute the stored procedure sp_start_job, SQL Server places the additional restriction on the job about ownership.

I'm editing to show why this is the case....

If you take a look at the T-SQL behind sp_start_job, you'll see it executes another stored procedure, sp_verify_job_identifiers. Well, sp_verify_job_identifiers uses the view msdb.dbo.sysjobs_view which is where we'll get stopped. If you do an sp_helptext on sysjobs_view, this is the result:



CREATE VIEW sysjobs_view  

AS  

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)

Notice is checks to see if the owner_sid matches that of the calling user. If the user doesn't match the sysadmin condition (or the other one for a target server role), this view will return back with no records and that causes the break down.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

Edited by - bkelley on 09/09/2002 11:54:58 AM

K. Brian Kelley
@kbriankelley

kerstin Old Hand Points: 302 More actions · Answer 2

so - it's as i have already feared - thanks for the quick reply.

is there really no solution to this task - no workaround - nothing?

can we not 'pretend' to be sysadmin for the duration of execution of the job ?

kerstin

Steven. SSCrazy Eights Points: 9806 More actions · Answer 3

NGS Software released a paper on threat profiling, which includes a hack on gaining admin rights via sql agent to gain admin rights. It may be worth taking a look at the code to see if this is a workaround.

http://www.nextgenss.com/papers/tp-SQL2000.pdf

Steven

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 4

I wouldn't rely on a hack to get around this issue. At some point Microsoft should address the issue and then the process breaks. Of course, don't ask me to hold my breath on fixes for some of the vulnerabilities Litchfield has found.

A simple solution is to create a table which is checked by a SQL Agent job on a regular time interval (1 minute, 5 minutes). Give the user the ability to execute a stored procedure which inserts a value in the table. The value is what the SQL Agent job will read and then use to execute its own sp_start_job.

This gives you a whole lot greater control. You can code the polling mechanism to only accept certain values, which means you control what jobs can be run. Also, you can audit to a separate table when user requested what job to be run when. While SQL Server does capture this information, it will eventually be overwritten. If you are doing your own auditing, you can keep the data as long as is sufficient.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley