July 7, 2003 at 4:39 am
Hi,
my problem is the impossibility to start a job from a web page which open a trusted connection to a SQL 2000 server. The Web server (IIS) rappresents the NT user logged in from the web page. That user, is the owner of the job. I've noticed that I've to add that user at least, to the dbo_owner role in the master database, to start the job regulary. I don't want to give that user more permissions then he needs.
I've tried to set the sql proxy agent
but it didn't work.
Any suggestions?
Thank you
July 7, 2003 at 4:56 am
I think you already posted this under:
"Job execution from asp page"
and a good answer was given.
"Insert a row into a table, have your job poll that table, when it finds a row, run the job you need to run."
To elaborate:
Set up a job that looks at a table every so often ("polls"). If that table contains a row then:
1 -- Delete the row (or mark it as being actioned)
2 -- Run whatever job
This polling job will run with appropriate permissions, i.e. not as whatever user context your IIS server is in.
To get the job to run from the web page, all the ASP page (or whatever) has to do is to insert a row into this table. It *will* have permission to do this, because you will have configured it so.
Once you have got this working, you can elaborate by perhaps returning a success/failure status to the ASP page through another table (or the same table - whatever).
July 7, 2003 at 5:07 am
Sorry .. I see you reformulated your question slightly. I still reckon that Andy's solution was good though.
If you want to restrict running the job to certain users, it might be a plan to have the asp page pass in the NT_USER value to the table which is polled. Your polling job could then determine whether that user had permission to run whichever job.
July 8, 2003 at 11:46 am
Is there any chance you can use MTS, because if you can, you can set up MTS impersonation. The users get access to the code in the package from their own login, but the package accesses SQL server using a predetermined login. You get good security and connection pooling.
July 9, 2003 at 10:56 am
Create a role in msdb and add the users or groups to that role which need to execute the job. Then add 2 lines to sysjobs_view:
OR ((ISNULL(IS_MEMBER(N'MyJobExecRole'), 0) = 1)
AND name like 'MyjobName%')
Where MyJobExecRole is the name of your newly created SQL role and MyJobName is the name of the job.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply