Start job from trigger w- non-admin user 1

[skicamel]

I've got a php page that, under certain, rare circumstances, adds a row to a table.

The goal is to receive an email notification whenever a row is inserted.

xp_sendmail is not an option (have 'sa' access to mssql, but little access on the box itself). MSSQL Notifications are out for the same reason.

I have an ActiveX script that can handle this and have been trying to figure out a way to get a trigger to call it. I tried setting up a job and using sp_start_job.

Logged in under sa, it works great. Under other non-admin users:

Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 38
The specified @job_id ('[job identifier]') does not exist.

I then tried to create a procedure that calls sp_start_job, and granted execute to the web user. Same result.

Any ideas how I can have an insert trigger an ActiveX script without significantly upgrading the web login's permissions? (The page is on a windows server where the php mail command does not work, so that's not an option).

Thanks for the help in advance. It's been a while. Glad to be back.

 

[mrdenny]

Change the job owner to the same account that the PHP page uses to log into the database.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[skicamel]

Thought I'd already tried that, but it looks like not. Now it will call the job, but still no go:

"Non-SysAdmins have been denied permission to run ActiveScripting job steps. The step failed."

Ideas?

 

[mrdenny]

Copy xp_smtp_sendmail to the server and register it. Use that to send mail via smtp instead of an Active X script.

If you can't do that use sp_OA procedures to sendmail. (There's some docs here and on microsoft.com on how to set them up.)

For non-sysadmins you'll need to enable the job step proxy account in the SQL Agent properties on the Job System page.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[skicamel]

Didn't even think to use sp_OA procedures. I am ashamed.

Major kudos on the other two. Very good information there. Looks like any of the 3 should work.

Thanks for the help. Haven't been here in ages, and not seeing good old Terry Broadbent in here gave me pause, but it looks like tek-tips is still in good hands.

Dan