Run Job as a different user

[99pshop]

I have a trigger that fires on insert.
The trigger then fires off a Job using sp_start_job. which executes a stored procedure.
The stored procedure my trigger calls uses sp_OACreate
The account I use from my webapp doesn't have permission to sp_OACreate and I prefer not to.

The job owner is the webapp user. but I am unable to change the run as to dbo.

Is it possible to have a non sysadmin job owner to use run as dbo?

 

[mrdenny]

Are you using SQL 2000 or SQL 2005?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com

 

[99pshop]

I am using MS SQL 2000 sp4

 

[mrdenny]

Then no, not really. In order for the web user to run the job the job must be owned by the user.

SQL 2000 doesn't have an EXECUTEAS like SQL 2005 does.

Why are you having the trigger start a job?
Why are you doing that is using the sp_OA procedures?


Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com

 

[99pshop]

I am using it to fire off email using an smtp component.

As soon as a user registers, the insert triggers off a process to send an email.

I have a job that runs every 10min as well, but as I will only get possibly 2 registration a day, it't inefficient to change the job to every minute.

 

[99pshop]

BTW, I dont have exchange server running on the server.

 

[mrdenny]

Go and download xp_smtp_sendmail. It's a DLL that you register as an extended stored procedure which uses smtp to send email.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com

 

[99pshop]

I have anSMTP installed on the server and have been using this with my job processes. My delema started with this new webapp function.

Will xp_smtp_sendmail run under a restricted account or will it need access to core procedures or sensitive components?

 

[mrdenny]

You will simply need to give the web login access to execute xp_smtp_sendmail.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com

 

[99pshop]

Thanks for that. I will give that a shot.