Maarten writes "Here is my scenario:We have a SQL-Server-2000 with 2 db's.(I do not know what service packs are installed, the server admin says:"It's up to date!". )The first is "our-content", the other is "their-content".The "our-content" database is the one the application runs on.The "their-content" database is where a content supplier synchronizes there data to, using a account "supplier" (we are using sql server authentication).The application we run needs the most recent data possible! Every second counts!!!So what I have done is make triggers on the "their-content" tables (owned by "supplier") with analyze the data an if it's interesting enough insert it in a other table, "Approved-content", owned by dbo.Now I want to make a trigger on the "dbo.Approved-content" table with created a Job and executes it.Code is like: EXEC MSDB.dbo.sp_add_job @job_name = @JobName, @enabled = 0, @owner_login_name = 'sa', @job_id = @job_id OUTPUT EXEC MSDB.dbo.sp_add_jobstep @job_id = @job_id, @step_name = 'step 1', @subsystem = 'TSQL', @command = @Command, @database_name = 'our-content', @database_user_name = 'sa', @output_file_name = @FileName, @flags = 2, @on_success_action = 1 EXEC @RC4 = MSDB.dbo.sp_start_job @JOB_ID = @JOB_ID
@Command being code witch has to be executed in the "our-content" database .As you can see I want the owner of the job to be "sa". I keep ending up with a job witch is owned by "supplier".Resulting in a failing job, because "supplier" doesn't have any rights in de "our-content" database.I don't want to make a new connection or something, because that would result in a situation where "supplier" will be able to see the username and password.I it possible?????"
Scripting a job "Executed as user" 
Posted - 2003-04-04 : 07:34:55
