Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2002-05-24 : 06:35:40
|
| I searched for some time now.I have ms_sqlserver 2000 on windows 2000 server.If I use the sp_start_job (from the msdb database) I can start Jobs with Enterprise manager or Query analyser. However... if a non-sysadmin tries to start a job (e.g. by hitting a button in an ASP page), I get the error:"The specified @job_name ('test job') does not exist."This appears to be a BUG in SQL 7 [url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;q201021[/url]If I change the ownership of the JOB to ASPNET user, this error is replaced by the following error in the Job-history."Non-SysAdmins have been denied permission to run CmdExec job steps. The step failed. "So this is what I understand:Only System Admins have the right to execute Jobs even if you give a normal user (like ASPNET) sufficient rights (without promoting him to system admin).To make a short story long, this is what I want:A simple user presses a button which triggers the server to import a text-file (by using DTS for example). How can I accomplish that???PS: I read another post herewhich say that sp_start_job could by started by normal users, but it doesn't work.I've seen a lot of post concerning this subject, but no satisfing answer. The key is that a non-admin user must be able to do it. If I give ASPNET user admin rights it works fine, but hey, this is not a perfect world, and I don't want an internetuser as admin on my SQL-server.Henri~~~SQL is nothing, writing it everything.Edited by - henrikop on 05/24/2002 06:45:25Edited by - henrikop on 05/24/2002 07:15:22 |
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2002-05-24 : 07:23:23
|
| To add some flavour to my former post:I understand that permission on the extended stored procedure xp_cmdshell is dangerous. Giving permission is a serious security issue. But how could I give a specific Stored Procedure the right to execute a cmdshell command? Then an anonymous user can fire the stored procedure (which starts the job), but cannot by-pass the stored procedure by executing shell command directly.If I am not clear, I can add information. Please say so.Henri~~~SQL is nothing, writing it everything. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-24 : 07:49:10
|
| (Imperfect, but more secure solution) How bout a proc that inserts the file metadata into a 'hopper' staging table and a job (owned by sa) that is sceduled to run every minute that sniffs the hopper for new file to pump in . . .<O> |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2002-05-24 : 07:54:55
|
| Hmmm, there's some merit in it.Do I get it right like this?:A normal user add's a record to a table. A job is looking every minute to that table and executes another job when a new record is found on this table?And could I use a trigger to start a job when a record is added? Or is this trigger fired with the rights of the user that insert the record?Henri~~~SQL is nothing, writing it everything. |
|
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2002-05-24 : 08:04:13
|
| You could create an error with an alert that starts the job.If you have a Dts packeage, you could also use the DTSrun Utility. |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2002-05-24 : 08:06:52
|
I believe that the DTSRun utility can only be used by users with Admin rights..  I am indeed using an DTSPackageHenri~~~SQL is nothing, writing it everything.Edited by - henrikop on 05/24/2002 08:07:21 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-24 : 08:16:20
|
| I think the trigger will inherit the credentials of the causal dml (I could be wrong...).I like there RAISERROR idea. Benefit: just about instantanious execution of the dts pkg (rather than waiting till the next 1 min cycle) and no constant overhead of a every minutes scheduled job. Disadvantage: the you will have to trap and handle that error so it isn't returned to you user and depending one the severity it may fill your sql logs . . .I best solution, is probably outside SQL. You could have an OS process that watched your target directory for new file and on arrival fire off the dts pkg (either through a SQL object or executing a sp_start_job ado command with sysadmin credentials)<O> |
|
|
|
|
|
|
|
|
Executing Jobs using sp_start_job